DAX has many functions to write conditional expressions. For example you might want to calculate sum of sales amount for all “Red” products. you can achieve it by using SUMX or Calculate, and functions such as IF or Filter to write a conditional expression for product color to be equal to “Red”. At the first you might think these functions will be have same in the result set, but there is a difference that should not be overlooked. In this post I’ll explain what type of problem might happen if you don’t use these functions wisely. If you want to learn more about Power BI; read Power BI book from Rookie to Rock Star.

Prerequisite

For running this example you would need to have a copy of AdventureWorksDW database in SQL Server, or you can download Excel version of it from here:

Brief of Functions

IF

“IF” is a conditional filtering expression function for DAX. You can simply write a conditional expression including Then and Else part of it. It simply works with this syntax;

IF(<conditional expression>, <what happens if true>, <what happens if false>)

Filter

“Filter” is a function that filters data set based on a custom filter. For example you can filter only products with “Red” color. Here is an example Filter expression;

FILTER( <table>, <filter condition>)

Sample Data Set

For this example, you need to bring FactInternetSales, and DimProduct into your Power BI Model. Relationship between these tables automatically should be detected by Power BI. It should be based on ProductKey between two tables. Here is how the relationship looks like;

Conditional SUM

There are multiple ways of calculating conditional sum in DAX. you can use SUMX or CALCULATE. both of these functions calculate an expression (In this case it would be sum of sales amount from FactInternetSales), based on a filter (which would be our conditional expression to find “Red” products). I will use SUMX in this example, but same concept applies on Calculate function as well. Here is how you can use SUMX for calculating sum of “Red” products;

Method 1 – SumX with FILTER

In the first method, I can use SUMX expression and filter the data set to be only “Red” products. Create a new Measure in FactInternetSales with this expression;

Sum of Red Products - With Filter = SUMX(
	                                    FILTER(FactInternetSales,
											   RELATED(DimProduct[Color])='Red')
										,FactInternetSales[SalesAmount]
										)

As you can see in the above expression, I have used a simple FILTER function to filter everything in FactInternetSales when Color or product is “Red”. I have used RELATED function because Color is a column in DimProduct and Related Function goes through the relationship from Many (FactInternetSales) to One (DimProduct) and allow us to do the filtering based on a column in a related table.

Method 2 – SumX with IF

We can achieve same result with SUMX and IF condition together. In this case the condition comes as IF statement in the expression part of SUMX. Here is the new measure’s code;

Sum of Red Products - With IF = SUMX(
	                                FactInternetSales,
									IF(RELATED(DimProduct[Color])='Red',
										FactInternetSales[SalesAmount],
										0)
                                    )

In this expression, instead of filtering data with FILTER function, I have used a conditional expression to identify if the color of product is “Red” or not, if it is “Red”, then I use SalesAmount for sum calculation, otherwise I use zero (means don’t summarize for other product colors).

Method 3 – Calculate with Simple Conditional Expression

There are many other methods of calculating the conditional sum, but just adding this one because it looks different; If I use Calculate Function with simple expression for checking the color of product as a new measure;

Sum of Red Products - Calculate Simple Expression = CALCULATE(
	                                                      SUM(FactInternetSales[SalesAmount]),
														  DimProduct[Color]='Red'
														)

Writing DimProduct[Color]=”Red” in this way is similar to writing a condition for every result set. The final result will be sum of Red Products.

Testing Results – Similar

If you bring both measures in Power BI as a Table Visual you will see the result of both are identical, and it will show you total sales amount for products with “Red” Color correctly;

Different Results

The result for measure is perfectly similar, however if you use one of these measures for a data set you will see the result of data set is different, and it changes the result significantly. For example if you use “Sum of Red Products – With Filter” only in a table with “Color” from DimProduct, here is what you will see:

If you use “Sum of Red Products – With IF” only in a table with “Color” from DimProduct, you will see different result;

In both cases, the grand total is similar. However the table with a FILTER measure will automatically filter the data set, and only shows result set for RED products, where the second table with IF measure, will show all products with zero in front of all colors, except Red. These two are VERY different in user point of view, while the final total value is similar. The reason is that IF just apply a conditional expression on the result set, where FILTER works differently and filters the data set to the custom filter expression. Notice that we don’t have any Visual, Report, or Page Level filter applied in this example. Filtering happened automatically because of FILTER function.

If you bring the last method’s result  into a table (Sum of Red Products – Calculate Simple Expression), you will see the calculation happens on every row in the result set. It won’t filter the data set, but the filter applies on calculating the final result for every row.

Which One to Use?

Like many other situations; It Depends! Are you only interested in final result (sum of sales amount for “Red” products in this example)? if yes, then FILTER gives you faster result, because it first filters the data set, and then apply SUM on it. However, it you want to show a detailed view of records, then FILTER function will also filter the data set, which might not be something you want, in those cases IF would give you the correct response. If you want to calculate percentage for each row, the Calculate method might generate more reliable result. Notice that there is nothing wrong about these functions, they are working exactly as they should. However, Not knowing their actual behavior might cause some confusion for you and your users. So use them wisely and don’t overlook their differences.

Download

You can download Power BI Demo file from here:

Save

Save

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.