Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gvg
Post Prodigy
Post Prodigy

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
MFelix
Super User
Super User

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

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

14 REPLIES 14
MFelix
Super User
Super User

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

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

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

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

gvg
Post Prodigy
Post Prodigy

@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]? 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hello @MFelix,

 

This what you showed is a very useful DAX technique which I did not know before and I'd like to get deeper into it. If I, for example, have many more fields in Table1, how can I filter on some other field in Table1 that is not summarized (i.e. not included in SUMMARIZE function)? When I try to do it, Power BI would not allow to enter any field, that is not included in SUMMARIZE function. I am looking to do something like this :

 

 

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 && Table1[NetQuantity]> 100
    )
)

I cannot put field NetQuantity in SUMMARIZE as summarization by it will give me wrong result for SalesX.

 

gvg
Post Prodigy
Post Prodigy

OK, found a way out. You just add another FILTER to CALCULATE:

 

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 
    ),
    FILTER (Sales, Table1[NetQuantity]> 100)
)

HI  @gvg,

 

Sorry for not responding to you I'm travelling in bussines, small hours to respond in the forum.

 

Glad you could work it out.

 

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Ok, good to know. Thanks @MFelix !

MFelix
Super User
Super User

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

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix

 

Here it is PBIX .

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.