Forum Discussion

tomperro's avatar
tomperro
Icon for Helper V rankHelper V
1 year ago
Solved

Filter Calculate Based on Slicer Selected

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)])
)
Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    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.

4 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Icon for Super User rankSuper User
    1 year ago

    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.

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    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

     

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    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.

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    1 year ago

    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.