Forum Discussion

scabral's avatar
scabral
Icon for Helper IV rankHelper IV
4 years ago
Solved

Dynamic DAX measures based on filter selections

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

  • Barthel's avatar
    Barthel
    4 years ago

    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:

2 Replies

  • Barthel's avatar
    Barthel
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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: