Dynamic DAX measures based on filter selections

[scabral]

Hi,

I have the following sample data table:

I also created a disconnected table with 3 rows to be used as a slicer like this:

Now i need to create a measure that will give me the counts for each account listed that have the same Classification, Occupancy, and Division based on the slicer selections dynamically.

So if I select just Classification slicer, then it should just give me the count of accounts that have the same classification as the account listed like this:

so for account 1234, there are 2 other accounts with the same classification of Education.  For account 2234, there are 0 other accounts with classification of Healthcare.

if I select Classification and Occupancy, then it should filter for both of those fields and come back like this:

so for account 1234 there is only 1 other account with classificaiton of Education and Occupancy of Schools.

I would need the DAX to be built dynamically based on either 1, 2, or 3 of the selections of each slicer category.

thanks

Scott

[Anonymous]

Hi @scabral,

I'd like to suggest you use selectedvalue and switch function to achieve your requirement. You can create a slicer with selections types and use switch to manage and redirect to different calculation expressions:

My Favorite DAX Feature: SELECTEDVALUE with SWITCH 

Regards,

Moonlight

[Barthel · Accepted solution]

Hey,

Not sure if this is the best way to write the code, but it seems to work:

Measuree =
VAR _Selections =
    VALUES ( 'Category Name'[Category Name] )
VAR _Classification =
    SELECTEDVALUE ( 'Table'[Client Classification] )
VAR _Division =
    SELECTEDVALUE ( 'Table'[Client Division] )
VAR _Occupancy =
    SELECTEDVALUE ( 'Table'[Predominant Occupancy] )
VAR _Result =
    CALCULATE (
        COUNTROWS ( 'Table' ) - 1,
        FILTER (
            ALL ( 'Table' ),
            IF (
                "Client Classification" IN _Selections,
                'Table'[Client Classification] = _Classification,
                TRUE
            )
                && IF (
                    "Client Division" IN _Selections,
                    'Table'[Client Division] = _Division,
                    TRUE
                )
                && IF (
                    "Predominant Occupancy" IN _Selections,
                    'Table'[Predominant Occupancy] = _Occupancy,
                    TRUE
                )
        )
    )
RETURN
    _Result

Result: