Filters are not applied to measures in table/matrix

[Maria9292]

Hi everyone!

I have table with columns that are these measures:

Calc_Green= CALCULATE(DISTINCTCOUNT('Table'[Qty)]),'Table'[Type]="Green"

Calc_Red= CALCULATE(DISTINCTCOUNT('Table'[Qty)]),'Table'[Type]="Red"

etc.

All columns and the slicer are from the same dataset. The problem is when the slicer applied (say I choose Red) values are shown in other columns as well although there should be null values in all columns except "Red"

What might be the problem here?

Thanks!

Maria

[amitchandak · Accepted solution]

@Maria9292 , Try like

Calc_Red= CALCULATE(DISTINCTCOUNT('Table'[Qty]),filter(all('Table'),'Table'[Type]="Red"))

But better to Type table joined with  your table and then use the filter on type table

Calc_Red= CALCULATE(DISTINCTCOUNT('Table'[Qty]),filter(all('Type'),'Type'[Type]="Red"))

[Anonymous · Accepted solution]

Hi @Maria9292,

Your formula will effect by slicers, when you apply one to the visual, these formulae with try calculated based on the condition. For the current row contents measure formula calculated, colors are being filtered by slicer so they will null value in the results. 

You can take a look at the following link about all functions to know more about this and add them to your formula to ignore the filter effect that applies to formulas:

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT 
Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@Maria9292 , Try like

Calc_Red= CALCULATE(DISTINCTCOUNT('Table'[Qty]),filter(all('Table'),'Table'[Type]="Red"))

But better to Type table joined with  your table and then use the filter on type table

Calc_Red= CALCULATE(DISTINCTCOUNT('Table'[Qty]),filter(all('Type'),'Type'[Type]="Red"))