Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
scabral
Helper IV
Helper IV

Dynamic DAX measures based on filter selections

Hi,

 

I have the following sample data table:

 

Capture.PNG

 

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

 

Capture.PNG

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:

 

Capture.PNG

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:

 

Capture.PNG

 

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

1 ACCEPTED SOLUTION
Barthel
Solution Sage
Solution Sage

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:

Barthel_0-1659981513274.png

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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
Solution Sage
Solution Sage

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:

Barthel_0-1659981513274.png

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.