Visual fails when Legend uses a field already used in the DAX

[Anonymous]

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?

[Jos_Woolley · Accepted 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

[Anonymous]

Thanks Jos,

This is exactly what is needed.  Looks like I need to brush up on my DAX.

🙂

[Jos_Woolley]

You're welcome! There aren't many people who don't need to brush up on their DAX, so you're certainly not alone! 😀

[Anonymous]

 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

[Tanushree_Kapse]

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!