IF and Filter are Different! Be Careful (DAX)

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2017-03-20_13h01_16

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:


Enter Your Email to download the file (required)

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;

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;

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;

2017-03-20_12h32_55

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;

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;

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;

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;

2017-03-20_12h50_05

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:

2017-03-20_12h52_34

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

2017-03-20_12h54_00

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.

2017-03-20_16h04_13

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:


Enter Your Email to download the file (required)

Save

Save

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad

Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.


4 Comments

  • Great article to show the differences between Filter and IF.

    If you still want to use the IF and obtain the first result you can change the measure to blank() instead of 0:
    Sum of Red Products – With IF = SUMX(
    FactInternetSales,
    IF(RELATED(DimProduct[Color])=”Red”,
    FactInternetSales[SalesAmount],
    Blank())

  • You are the first one to provide me with an Excel version of AdventureWorksDW database, I’ve been expecting it for quite a long time. I do appreciate it.

Leave a Reply to vvelarde Cancel reply

Your email address will not be published. Required fields are marked *