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 ...
  • 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:

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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