Forum Discussion
Getting matrix values dynamically based on the hierarchy level
I have row hierarchy of 4 levels in a matrix. If I go to 3rd level, the values should take from 'x' column of fact table and If I go to 4th level, the values should take from 'y' column of fact table. My dataset is live conncted with the tabular model. The format will be as shown below:
Owner
Level1
Level2
Level3 value of ‘X’ column
Level4 value of ‘Y’ column
Level4 value of ‘Y’ column
Level4 value of ‘Y’ column
Level3 value of ‘X’ column
Level4 value of ‘Y’ column
Level4 value of ‘Y’ column
As I am new to Power BI, I am not able to figure out how to do this. Is there any way like creating a calculated column or is there any other approach?
Hi v-srekom
Hard to generate results without the dummy pbix/sample data. but I believe you are referring to the ISINSCOPE/ISFILTERED function, here're some articles could provide you more hints:
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
4 Replies
Explaining in detail:
I have 4 levels of row hierarchy,
- In the 1st 2 levels, Owner values should be empty
- In the 3rd level, Owner values should be from IO Owner column of the fact table because the hierarchy is IO Numbers
- In the 4th level, Owner values should be from PO Owner column of the fact table because the hierarchy is PO Numbers
As the owner values are aliases, I am not sure whether I can expose or not.
v-srekom - See if MM3TR&R helps: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/m-p/411443#M150
Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi v-srekom
Hard to generate results without the dummy pbix/sample data. but I believe you are referring to the ISINSCOPE/ISFILTERED function, here're some articles could provide you more hints:
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
