12 Essential Power BI DAX Formulas Every Report Creator Should Master

Use cases:

• Extract new insights from your data
• Add new calculated columns to tables without affecting existing data

SELECTCOLUMNS: Create a New Table with Just the Fields You Want

What it does:

SELECTCOLUMN works similarly to the previously described ADDCOLUMNS, but instead of adding new columns to an existing table, it will create a brand-new table. It will contain just your selected columns – copied as-is or with applying your provided expressions. 

Example:

Our ‘Sales’ table has many columns we don’t need, and we want to reduce it to just two: Customer ID and Amount. SELECTCOLUMNS will do just that. 

Use cases:

• Simplify data and optimize queries
• Transform or modify data without affecting the original table
• Create lookup/reference tables
• Control user access and omit unnecessary data

SUMMARIZE: Create New, Summarized Tables

What it does:

This one is crucial for simplifying your data model, reducing the query sizes and improving report performance. SUMMARIZE will group your data using one or more category columns (e.g. “Region”, “Segment”, “Datetime” etc.) and create a new table which has only summarized values, instead of referencing the entire table. If you have used pivot tables in Excel, this will be very familiar.

Example:

In our fact table, we have a row for every single purchase. We don’t need that; we just want a summarized table that shows the average product sale price per each country so we can improve the pricing strategy for each market.

So, the new table will have a row for each country name in the Sales[Country] column, and it will take all rows for each country and divide their total ‘Amount’ with ‘Quantity’. The expression in our example uses the previously discussed DIVIDE, but you can use other expressions in its stead to achieve your desired results.

Use cases:

• Simplifying data & optimizing queries
• Visualizing category-based insights (e.g. total revenue per country or sold units per brand)

FILTER: Keep Only Rows You Want

What it does:

You have a table of all data, and you need to quickly create a filtered table that has only rows that match a specific condition. The new table will keep rows where your condition is true (like numeric values or matching text) and omit those where it is false.

Example:

One way to use FILTER is by matching text strings using the “=” operator; for example, Sales[Country] = “United States” to see only sales made in the USA. Or you can use a numerical column for the condition – here, we wanted to create a table with only high-paying customers, so FILTER kept only rows where ‘Amount’ exceeded 500$. All columns from the original table were copied to the new one, but only for rows that matched our condition.

Use cases:

• Filtering data by a value condition (e.g. high-paying customers, risk groups, etc.)
• Filtering data to a category (e.g. region, country, brand, demographic group etc.)
• Filtering data to a specific time period

RELATED: Pull Data from Linked Tables

What it does: 

If your data model has relationships between multiple tables, you will most likely run into scenarios where you want to reference a column from a different table. RELATED will fetch a value from a related table into the current one based on the relationship.

Example:

Let's return back to FILTER that we previously discussed: you can use RELATED in cases where you need to use data from another table in your filter. For example, you are storing data about customers – region, country, name, etc. – in a different dimension table, and you want to use that data to filter your fact table of all your sales (e.g. create a table with only sales in Asia).

Use cases:

• Enrich fact tables with dimension data
• Display additional details for more insights (e.g. Tooltip Fields, etc.)
• Use in other expressions like FILTER, SELECTCOLUMNS, or CALCULATE

IF: Do "This" or "That"

What it does:

‘If’ statements are the cornerstone of almost any programming language, and DAX is no exception. It will check the value against your defined condition and give one result if the condition is met, and another result if it’s not.

Example:

You want to tag each sale as ‘High’ or ‘Low’ based on its value – if it is above $250, then it is high, but if it is below that, it is low. IF will check each value and return the correct label. The expression consists of the condition, output when the condition is met, and output if it is not met.

Use cases:

• Labels, tooltip fields, simple alerts etc.
• Visualizing as gauge or pie chart categories (e.g. how many % of all sales are above $250)
• Conditional formatting (e.g. “green”, “red” based on condition)

SWITCH: Multi-Condition “IF” 

What it does:

When you want to have multiple conditions, you don’t need to create additional IF expressions for each. SWITCH allows you to stack multiple conditions and outputs in one expression. Basically, if the condition 1 is met, do one thing, otherwise check if the condition 2 is met and do another thing; and for the rest of values, do something else.  

Example:

In the previous example with IF, we could only categorize sales in two categories – high and low. But with SWITCH, we can add more tiers – now, values above $500 will return “Very High”, anything between 251 and 500 is “High”, deals between 101 and 250 are “Medium”, and everything else is “Low”. 

The latter example used ‘more-than’ (>) symbol, but you can use other operators as well. For example, with ‘equal to’ (=) operator, SWITCH can also be used to find exactly matching rows and apply the condition only to them.

Use cases:

• Bucketing (pricing tiers, risk groups, performance levels)
• Labels, tooltip fields
• Conditional formatting (based on numeric or text values)

RANKX: Rank Items by Value

What it does:

RANKX assigns a rank to each row based on the value of a given measure. It will go through all rows in the column and return a number starting from 1 (largest value). 

Example:

You want to know which countries have the most total sales. RANKX will go through each country in the Sales[Country] column and assign a rank number based on Sales[Total Sales] column values, so you can sort them to see a leaderboard of your best-performing markets.

One way to use this is to create custom rank-based sorting order for visualizations. While most column or bar chart visuals already sort columns by default based on series values, RANKX could be used in cases where you want to use other metrics for sorting or use a measure that combines multiple values. You can then add the new measure to one of the 25 ‘Series’ fields in Combo PRO and select it for sorting purposes (and hide it from the chart if you want).

Use cases:

• Visualize leaderboards, show rank in tooltip fields
• Sort table rows, determine column chart order
• Use as filter (e.g. show only Top 10 or bottom 10)

Final Thoughts

These twelve essential DAX formulas are a great starting point for Power BI report creators, and with these, you can start building reports that deliver more insights than the ones present in the dataset. You can filter data based on context, perform calculations across multiple columns or even tables for new metrics, and automate bucketing or labelling. 

Of course, to let your measures, calculated columns or filters truly shine, you need advanced visuals for your reports – and with Drill Down Visuals by ZoomCharts, you can use seamless cross-filtering, multiple drill down levels and endless customization options to create the most insightful and intuitive reports.

FAQ

What is DAX in Power BI?

DAX is the language that is used to perform calculations and transform data in Power BI.

What is the difference between DAX measures and calculated columns?

While both are similar and can use DAX expressions to perform calculations, measures return values on-demand (i.e., when a visual requests them) whereas calculated columns exist as part of the table and are calculated on report load.

How to create a DAX measure in Power BI?

To create a new DAX measure, press the New Measure button in the Home tab of Power BI Desktop’s top ribbon. You will be able to enter your desired DAX expression and create a measure. Another way to do that is by right-clicking in the Data pane on the right side and selecting New Measure (or New Column if you want a calculated column instead).

How to calculate percentage in Power BI with DAX?

For this, you can use DIVIDE –provide the first column as numerator and the second column as denominator, and the division between will be returned as a decimal (e.g. 150 / 200 = 0.75). In the top ribbon, set the format to Percentage, and 0.75 will be converted to 75%.