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.
I have a simple table of Sales by Product Types.
I want to show Sales by Product Type for all 'Non Food' Sales in a Donut Visual.
When I bake the NOT 'Food' filter into the DAX, the donut fails to return the correct Product Type Split.
I have to create a 'Non Food Flag' column to use in the DAX so that the Product Type still works in the visual.
Any ideas why OR if there is a better way to achieve this?
Solved! Go to Solution.
Hi,
When you use:
Non Food Sales Count =
CALCULATE ( COUNT ( Sales[Sales ID] ), NOT ( Sales[Product Type] = "Food" ) )
i.e. the expression used as the filter within CALCULATE does not incorporate the FILTER function, it implicitly incorporates the column being passed within that expression to the ALL function, i.e. the above is equivalent to:
Non Food Sales Count = CALCULATE(COUNT(Sales[Sales ID]),FILTER(ALL(Sales[Product Type]),NOT(Sales[Product Type]="Food")))
This implicit ALL restores (overrides) the filtering coming from the Product Type, and so returns the same value for all Product Types, i.e. 7 (equivalent to the total sales which are either Electronics or Clothing).
Use either a version with FILTER:
Non Food Sales Count FILTER =
CALCULATE (
COUNT ( Sales[Sales ID] ),
FILTER ( Sales, NOT ( Sales[Product Type] = "Food" ) )
)
or else amend your version using KEEPFILTERS:
Non Food Sales Count KEEPFILTERS =
CALCULATE (
COUNT ( Sales[Sales ID] ),
KEEPFILTERS ( NOT ( Sales[Product Type] = "Food" ) )
)
Regards
Hi,
When you use:
Non Food Sales Count =
CALCULATE ( COUNT ( Sales[Sales ID] ), NOT ( Sales[Product Type] = "Food" ) )
i.e. the expression used as the filter within CALCULATE does not incorporate the FILTER function, it implicitly incorporates the column being passed within that expression to the ALL function, i.e. the above is equivalent to:
Non Food Sales Count = CALCULATE(COUNT(Sales[Sales ID]),FILTER(ALL(Sales[Product Type]),NOT(Sales[Product Type]="Food")))
This implicit ALL restores (overrides) the filtering coming from the Product Type, and so returns the same value for all Product Types, i.e. 7 (equivalent to the total sales which are either Electronics or Clothing).
Use either a version with FILTER:
Non Food Sales Count FILTER =
CALCULATE (
COUNT ( Sales[Sales ID] ),
FILTER ( Sales, NOT ( Sales[Product Type] = "Food" ) )
)
or else amend your version using KEEPFILTERS:
Non Food Sales Count KEEPFILTERS =
CALCULATE (
COUNT ( Sales[Sales ID] ),
KEEPFILTERS ( NOT ( Sales[Product Type] = "Food" ) )
)
Regards
Thanks Jos,
This is exactly what is needed. Looks like I need to brush up on my DAX.
🙂
You're welcome! There aren't many people who don't need to brush up on their DAX, so you're certainly not alone! 😀
Hi Tanushree,
Thanks for the input it is really appreciated.
For info, the example provided is a simplification of a real business case DAX measure. It is imperative that the filter is built into the DAX. This measure will be reused across multiple reports and without the filter in the DAX, the measure is incomplete\incorrect.
Thanks
Hi @Anonymous ,
You can use visual level filter for donut chart.
Filter out Food in basic filtering.
Mark this as a solution if I answered your question. Kudos are always appreciated.
Thanks!
Covering 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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |