Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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:
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
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:
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |