Forum Discussion

AlexW24's avatar
AlexW24
New Member
11 months ago
Solved

Distinct with multiselect slicer

How can I update the following to allow multiple selected values in a slicer?   Distinct Apps = CALCULATE( DISTINCTCOUNT(AppUsage[AppName]), FILTER( AppUsage, AppUsage[U...
  • tayloramy's avatar
    tayloramy
    11 months ago

    Hi ,  

    You’re running into a classic SELECTEDVALUE limitation: it only returns a single value. With multiple developers selected, SELECTEDVALUE goes blank, so your filter logic no longer excludes the chosen names. 

    Use the IN operator against the selected list, and guard for the “no selection” case so you don’t exclude everyone by accident:

    Distinct Apps =
    VAR HasDevFilter =
    ISFILTERED(DeveloperTable[UserName])
    VAR SelectedDevs =
    VALUES(DeveloperTable[UserName]) -- all developers currently selected in the slicer
    RETURN
    IF (
    NOT HasDevFilter, -- nothing selected: show all apps (no exclusions)
    DISTINCTCOUNT ( AppUsage[AppName] ),
    CALCULATE (
    DISTINCTCOUNT ( AppUsage[AppName] ),
    REMOVEFILTERS ( DeveloperTable[UserName] ), -- ignore slicer’s direct filter first
    NOT ( AppUsage[UserName] IN SelectedDevs ) -- then exclude selected developers
    )
    )

     

    If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

    AlexW24

tayloramy's avatar
tayloramy
Icon for Super User rankSuper User
11 months ago

Hi ,  

You’re running into a classic SELECTEDVALUE limitation: it only returns a single value. With multiple developers selected, SELECTEDVALUE goes blank, so your filter logic no longer excludes the chosen names. 

Use the IN operator against the selected list, and guard for the “no selection” case so you don’t exclude everyone by accident:

Distinct Apps =
VAR HasDevFilter =
ISFILTERED(DeveloperTable[UserName])
VAR SelectedDevs =
VALUES(DeveloperTable[UserName]) -- all developers currently selected in the slicer
RETURN
IF (
NOT HasDevFilter, -- nothing selected: show all apps (no exclusions)
DISTINCTCOUNT ( AppUsage[AppName] ),
CALCULATE (
DISTINCTCOUNT ( AppUsage[AppName] ),
REMOVEFILTERS ( DeveloperTable[UserName] ), -- ignore slicer’s direct filter first
NOT ( AppUsage[UserName] IN SelectedDevs ) -- then exclude selected developers
)
)

 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

AlexW24

AlexW24's avatar
AlexW24
New Member
11 months ago

tayloramy Thank you for the solution!  Prior to coming here for help, I did try to make it work with the "IN" function but I was nowhere close to what you provided.  This seems like an overly complicated formula just to allow multiple selected items.  I hope Microsoft makes this easier in the future.  

 

Thanks again for the solution!