Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have a clustered bar chart with hierarchical data on Axis and couple of measures in Value. Sample source data looks like below:
Here the hierarchy is Object->Sub-Object->Leaf-Object.
The issue I have is that I want to display the Staging Count and Final Count values where the Main flag for Object='Y' for Parent i.e. Object.
Similarly, I want to display count values where the Main flag for Sub-Object ='Y' for Sub-object when I drill down to the Sub-object level.
Current behaviour of PBI is its summing using SUM(Staging Count) for object and sub-object levels, by default.
What I have tried: Used analytical functions in Oracle to find the right count that has flag set to 'Y' and use FIRST() to display the count. But the problem with that is when I drill down the count value does not change which is expected from PBI. How do I dynamically change the flag fields at different levels of hierarchy and display its count.
Thanks
Rav
Solved! Go to Solution.
Hi @saravananravim ,
We can create two measures using following DAX to meet your need.
StagingCountMeasure = IF ( ISINSCOPE ( 'Table'[Leaf-Object] ), CALCULATE ( SUM ( 'Table'[Staging Count] ) ), IF ( ISINSCOPE ( 'Table'[Sub-Object] ), CALCULATE ( SUM ( 'Table'[Staging Count] ), FILTER ( 'Table', 'Table'[Main flag for Sub-Object] = "Y" ) ), CALCULATE ( SUM ( 'Table'[Staging Count] ), FILTER ( 'Table', 'Table'[Main flag for Object] = "Y" ) ) ) )
FinalCountMeasure = IF ( ISINSCOPE ( 'Table'[Leaf-Object] ), CALCULATE ( SUM ( 'Table'[Final Count] ) ), IF ( ISINSCOPE ( 'Table'[Sub-Object] ), CALCULATE ( SUM ( 'Table'[Final Count] ), FILTER ( 'Table', 'Table'[Main flag for Sub-Object] = "Y" ) ), CALCULATE ( SUM ( 'Table'[Final Count] ), FILTER ( 'Table', 'Table'[Main flag for Object] = "Y" ) ) ) )
Then we can create the chart as you wish.
BTW, pbix as attached.
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
Hi @saravananravim ,
We can create two measures using following DAX to meet your need.
StagingCountMeasure = IF ( ISINSCOPE ( 'Table'[Leaf-Object] ), CALCULATE ( SUM ( 'Table'[Staging Count] ) ), IF ( ISINSCOPE ( 'Table'[Sub-Object] ), CALCULATE ( SUM ( 'Table'[Staging Count] ), FILTER ( 'Table', 'Table'[Main flag for Sub-Object] = "Y" ) ), CALCULATE ( SUM ( 'Table'[Staging Count] ), FILTER ( 'Table', 'Table'[Main flag for Object] = "Y" ) ) ) )
FinalCountMeasure = IF ( ISINSCOPE ( 'Table'[Leaf-Object] ), CALCULATE ( SUM ( 'Table'[Final Count] ) ), IF ( ISINSCOPE ( 'Table'[Sub-Object] ), CALCULATE ( SUM ( 'Table'[Final Count] ), FILTER ( 'Table', 'Table'[Main flag for Sub-Object] = "Y" ) ), CALCULATE ( SUM ( 'Table'[Final Count] ), FILTER ( 'Table', 'Table'[Main flag for Object] = "Y" ) ) ) )
Then we can create the chart as you wish.
BTW, pbix as attached.
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
84 | |
66 | |
62 | |
62 |
User | Count |
---|---|
199 | |
120 | |
110 | |
79 | |
69 |