cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gvg Member
Member

Testing how Filter works

Hi experts,

 

I came accross this strange FILTER behaviour. Maybe it is because of my lack of understanding of DAX. I have this measure defined to test CALCULATE-FILTER behaviour:

 

 

Test filter = CALCULATE(SUM(Table1[Sales]), FILTER(Table1, Table1[Sales] < 70))

However, I must say, I am rather surprised what I get:

 

 

Untitled.png

Can you help, please, understand, why am I getting this result instead of obvious 90? 

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Testing how Filter works

Hi @gvg,

 

Looking at your model you have several lines and your table shows the sum of the several regions if in your table you filter out the  values that era lower than 70 you will get the 180:

 

70.png

 

When you use measures this are based on context so when you add filter to your measure you are getting in the card visual all the lines that are below 70 (green part) that sums up 180.

auvergauverne, 

In you case you want to calculate the regions that have a total sales that are below 70 in this case  only Auverne, Picardy and Sardinia you need to redo your measure to a different context:

 

Test filter =
CALCULATE (
    SUM ( Table1[Sales] );
    FILTER (
        SUMMARIZE (
            ALL ( Table1[Country]; Table1[Region]; Table1[Sales] );
            Table1[Country];
            Table1[Region];
            "sales"; SUM ( Table1[Sales] )
        );
        [sales] < 70
    )
)

As you can see below the total is 90.

.filter.png

Explanation of meausre: In the filter I'm making a calculated table that reflects the summary table that you have  so in this case I'm using the Country and Region and making the sum of the sales for those columns (grouping sales) so the filter is made not over the full dataset but over the dataset with the grouping by region so when you filter only the ones that are lower than 70 you will get the correct value.

 

Attach the PBIX file for you to review it.

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
Super User
Super User

Re: Testing how Filter works

Hi @gvg,

 

As I said measures are calculated based on context so when you have you table visual the country and region gives you context over the calculated measure, however when you put the same measure on a card the context of that measure is different and is provided by the filter /slicer that you aplly to that specific visual.

 

As you can see below if you add your measure to the table you have you will only get numbers on the country/regions that  correspond to the filter you  place in because the table is giving the context (grouping) that the calculation needs to provide the correct result, so depending on the type of visual the same measure can give you different results.

 

Also as you can see below the measure that I wrote if you add it to the table you will always get 90 for all rows, since the use of ALL in the filter overlapping the table context that we define in each row.

 

foilll.png

 

Regards,

MFelix

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Testing how Filter works

Hi @gvg,

 

No because you are summing the total sales the SalesX is a column to filter out the information you need it's not included in the calculation as a variable.

 

To make it that way you should have the temporary table calculated as a variable and then use it on the SUM.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




14 REPLIES 14
Super User
Super User

Re: Testing how Filter works

Hi @gvg,

 

The measure as you have has everything to work correctly, I have copied your table and your measure and the result was 90.

 

How do you have your model setup is everything in the same table do you have dimension tables?

 

Regards,

 

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




gvg Member
Member

Re: Testing how Filter works

@MFelix

 

Here it is PBIX .

Super User
Super User

Re: Testing how Filter works

Hi @gvg,

 

Looking at your model you have several lines and your table shows the sum of the several regions if in your table you filter out the  values that era lower than 70 you will get the 180:

 

70.png

 

When you use measures this are based on context so when you add filter to your measure you are getting in the card visual all the lines that are below 70 (green part) that sums up 180.

auvergauverne, 

In you case you want to calculate the regions that have a total sales that are below 70 in this case  only Auverne, Picardy and Sardinia you need to redo your measure to a different context:

 

Test filter =
CALCULATE (
    SUM ( Table1[Sales] );
    FILTER (
        SUMMARIZE (
            ALL ( Table1[Country]; Table1[Region]; Table1[Sales] );
            Table1[Country];
            Table1[Region];
            "sales"; SUM ( Table1[Sales] )
        );
        [sales] < 70
    )
)

As you can see below the total is 90.

.filter.png

Explanation of meausre: In the filter I'm making a calculated table that reflects the summary table that you have  so in this case I'm using the Country and Region and making the sum of the sales for those columns (grouping sales) so the filter is made not over the full dataset but over the dataset with the grouping by region so when you filter only the ones that are lower than 70 you will get the correct value.

 

Attach the PBIX file for you to review it.

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




gvg Member
Member

Re: Testing how Filter works

OK, this is great explanation. Thank you so much!

 

I have been trying to understand the theory by the Italians that FILTER in CALCULATE works in the original filter context. So my reasoning was that Country and Regions are original filters within which my CALCULATE (....FILTER(...) ) should work. Doesn't look like so.

Highlighted
Super User
Super User

Re: Testing how Filter works

Hi @gvg,

 

As I said measures are calculated based on context so when you have you table visual the country and region gives you context over the calculated measure, however when you put the same measure on a card the context of that measure is different and is provided by the filter /slicer that you aplly to that specific visual.

 

As you can see below if you add your measure to the table you have you will only get numbers on the country/regions that  correspond to the filter you  place in because the table is giving the context (grouping) that the calculation needs to provide the correct result, so depending on the type of visual the same measure can give you different results.

 

Also as you can see below the measure that I wrote if you add it to the table you will always get 90 for all rows, since the use of ALL in the filter overlapping the table context that we define in each row.

 

foilll.png

 

Regards,

MFelix

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




gvg Member
Member

Re: Testing how Filter works

Again, thank you @MFelix for clarification. Indeed, one has to be very careful, what filters are there in the visual.

gvg Member
Member

Re: Testing how Filter works

@MFelix

Some follow-up on the measure you advised (slightly changed for clarity):

 

Test filter =
CALCULATE (
    SUM ( Table1[Sales] );
    FILTER (
        SUMMARIZE (
            ALL ( Table1[Country]; Table1[Region]; Table1[Sales] );
            Table1[Country];
            Table1[Region];
            "salesX"; SUM ( Table1[Sales] )
        );
        [salesX] < 70
    )
)

How does CALCULATE know what to sum? I presume SUMMARIZE produces a new table where there is no field Table1[Sales] but instead there is field [salesX]? 

Super User
Super User

Re: Testing how Filter works

Hi @gvg,

 

The calculate is summing the sales value, however what we do in the filter is to pick all the selected columns and then make a new table that is consider for the calculations so you are making a temporary table with the groupings we want and then inside that new subset of data making the calculations, as you can see we use the SalesX to filter out the numbers and SalesX is a column that we only have in the temporary table.

 

So basically we are summing the total of the sales from a temporary table that is calculated from our Sales table, since the measures are calculated on loading this temporary table is always recalculated when you update your data or change the context of your visuals.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




gvg Member
Member

Re: Testing how Filter works

Yes, exactly how I thought. But then shouldn't we use CALCULATE ( SUM ( [salesX]) ,,,,  ?