Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
See attached example data
My data has a planned number of people (planned column) per category
There's 2 categories - the master column and legend where each legend fall beneath a master to establish a hierarchy ie 1 has 11, 12, and 13
All have hardkeyed plans. The masters have their own independent from a legend value.
How can I have a measure that switch between the correct master then legend after filtering ie If i have a master filter and select 2, 98 appears then if i filter again to 22, 95 appears.
Solved! Go to Solution.
Hi,
Do you want to show this measure in matrix visual?
If so, please try this measure:
Measure =
IF (
CALCULATE ( DISTINCTCOUNT ( 'Table'[Inside] ), ALLSELECTED ( 'Table' ) )
<> CALCULATE ( DISTINCTCOUNT ( 'Table'[Inside] ), ALL ( 'Table' ) ),
IF (
ISINSCOPE ( 'Table'[Inside] ),
BLANK (),
CALCULATE (
MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Plan] ),
FILTER ( 'Table', 'Table'[Master] = SELECTEDVALUE ( 'Table'[Master] ) )
)
),
CALCULATE (
MAX ( 'Table'[Plan] ),
FILTER (
'Table',
'Table'[Master] = SELECTEDVALUE ( 'Table'[Master] )
&& 'Table'[Inside] = SELECTEDVALUE ( 'Table'[Inside] )
)
)
)
When only selecting master in slicer, it shows:
When both selecting master and inside in slicers, it shows:
Here is my changed pbix file:
Best Regards,
Giotto Zhi
Hi,
Please try this measure:
Measure =
IF (
SELECTEDVALUE ( 'Table'[Inside] ) = BLANK (),
CALCULATE (
MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Plan] ),
FILTER ( 'Table', 'Table'[Master] = SELECTEDVALUE ( 'Table'[Master] ) )
),
CALCULATE (
MAX ( 'Table'[Plan] ),
FILTER (
'Table',
'Table'[Master] = SELECTEDVALUE ( 'Table'[Master] )
&& 'Table'[Inside] = SELECTEDVALUE ( 'Table'[Inside] )
)
)
)
Choose this measure as a Card visual, when you only select master in slicer, it shows:
When you continue to select Inside in slicer, it shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
Can this be applied to a measure rather than a card?
Hi,
Do you want to show this measure in matrix visual?
If so, please try this measure:
Measure =
IF (
CALCULATE ( DISTINCTCOUNT ( 'Table'[Inside] ), ALLSELECTED ( 'Table' ) )
<> CALCULATE ( DISTINCTCOUNT ( 'Table'[Inside] ), ALL ( 'Table' ) ),
IF (
ISINSCOPE ( 'Table'[Inside] ),
BLANK (),
CALCULATE (
MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Plan] ),
FILTER ( 'Table', 'Table'[Master] = SELECTEDVALUE ( 'Table'[Master] ) )
)
),
CALCULATE (
MAX ( 'Table'[Plan] ),
FILTER (
'Table',
'Table'[Master] = SELECTEDVALUE ( 'Table'[Master] )
&& 'Table'[Inside] = SELECTEDVALUE ( 'Table'[Inside] )
)
)
)
When only selecting master in slicer, it shows:
When both selecting master and inside in slicers, it shows:
Here is my changed pbix file:
Best Regards,
Giotto Zhi
Did not get it completely
https://community.powerbi.com/t5/Desktop/Hierarchy-of-calculated-measures/td-p/117433
https://visualbi.com/blogs/microsoft/powerbi/customizing-hierarchy-measures-dax-using-inscope/
The values are displaying correctly when inside a matrix but not when plotted on a line graph
I'm only getting blank values using isinscope
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |