Filtering Problem

[Anonymous]

Hi all,

I created a measure to count based on a specific text value in a column.

This measure is showings  the number of available monitors in my company by using this logic:

CountAvailableAssets(FullList) =

CALCULATE ( DISTINCTCOUNT('Asset Management Full version'[SBMBarcode]), 'Asset Management Full version'[Available]= "Yes" )+0

 I used the same logic for all the highlighted  cards shown in the screen shot (available, assigned, scrap and giveaway).

The problem is when I try and apply the availability filter on it, it doesn't work.

as you can see I applied the filter to only available but all others are still showing.

how can I set others to be ZERO instead of showing all numbers when I filter to one of them

[Pragati11 · Accepted solution]

Hi @Anonymous ,

To make the external filters work, modify your DAX as follows:

CountAvailableAssets(FullList) =

CALCULATE ( DISTINCTCOUNT('Asset Management Full version'[SBMBarcode]), FILTER(ALLSELECTED('Asset Management Full version'), 'Asset Management Full version'[Available]= "Yes" ))+0

Thanks,

Pragati

[mahoneypat · Accepted solution]

The filter in your measure is overriding the filter from the Filter.  To avoid that, you can add an IF before your expression.

CountAvailableAssets(FullList) =
IF (
    "Yes"
        IN VALUES ( 'Asset Management Full version'[Available] ),
    CALCULATE (
        DISTINCTCOUNT ( 'Asset Management Full version'[SBMBarcode] ),
        'Asset Management Full version'[Available] = "Yes"
    ) + 0
)

Pat

[Pragati11 · Accepted solution]

Hi @Anonymous ,

To make the external filters work, modify your DAX as follows:

CountAvailableAssets(FullList) =

CALCULATE ( DISTINCTCOUNT('Asset Management Full version'[SBMBarcode]), FILTER(ALLSELECTED('Asset Management Full version'), 'Asset Management Full version'[Available]= "Yes" ))+0

Thanks,

Pragati