Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsOK, 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.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsAgain, thank you @MFelix for clarification. Indeed, one has to be very careful, what filters are there in the visual.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYes, 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |