Filter Calculate Based on Slicer Selected

[tomperro]

Is it possible to alter the filters in a calculate dax formula based on slicer selections?

I have 3 slicers, Department, Division and Zone and I want to get a count of items based on each selection.

If the user only selectes Department, then show the count of items for that Department. 

If the user only selectes Division , then show the count of items for that Division . 

If the user only selectes Zone , then show the count of items for that Zone . 

I have tried Switch and If and even || but I can not get it to work.

 CALCULATE(  

        SUM('Items '[Total_Items]),

        SELECTEDVALUE('Items'[Zone]) = SELECTEDVALUE(Location[Zone]) ||

        SELECTEDVALUE('Items '[Department]) = SELECTEDVALUE(Location[Department]) ||

        SELECTEDVALUE('Items '[Division]) = SELECTEDVALUE(Location[Division)])
)

[rohit1991 · Accepted solution]

Hi @tomperro

The reason your measure isn’t behaving correctly is because SELECTEDVALUE() only works when one value is selected. If multiple values (or none) are selected, it returns blank and your CALCULATE doesn’t apply the filters as expected.

Here’s a measure that dynamically applies the filters only for slicers with selections:

Dynamic Item Count = 
CALCULATE (
    COUNT ( Item[ItemId] ),
    // Apply Zone filter only if any selection is made
    IF (
        ISFILTERED ( Location[Zone] ),
        KEEPFILTERS ( VALUES ( Location[Zone] ) )
    ),
    // Apply Department filter if selected
    IF (
        ISFILTERED ( Location[Department] ),
        KEEPFILTERS ( VALUES ( Location[Department] ) )
    ),
    // Apply Division filter if selected
    IF (
        ISFILTERED ( Location[Division] ),
        KEEPFILTERS ( VALUES ( Location[Division] ) )
    )
)

This will count the items based on any combination of selected slicers. If a slicer isn’t used, it’s ignored exactly what you're after.

[V-yubandi-msft]

Hi @tomperro ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @tomperro ,

Thank you for reaching out to the Microsoft Fabric Community.

The @pankajnamekar25 , solution works perfectly for your needs. It adjusts dynamically to slicer selections for Department, Division, or Zone, ensuring accurate results.

Your formula didn't work because || doesn't account for slicer activity properly. Their measure uses ISFILTERED to handle slicers effectively. Try it out and let us know how it goes.

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly

[pankajnamekar25]

Hello @tomperro 

try this measure

Dynamic Item Count =
CALCULATE(
SUM('Items'[Total_Items]),
IF(
ISFILTERED(Location[Zone]),
'Items'[Zone] = SELECTEDVALUE(Location[Zone]),
TRUE()
),
IF(
ISFILTERED(Location[Department]),
'Items'[Department] = SELECTEDVALUE(Location[Department]),
TRUE()
),
IF(
ISFILTERED(Location[Division]),
'Items'[Division] = SELECTEDVALUE(Location[Division]),
TRUE()
)
)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.