Testing how Filter works

[gvg]

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:

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

[MFelix · Accepted solution]

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:

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.

.

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

[MFelix · Accepted solution]

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.

Regards,

MFelix