Getting matrix values dynamically based on the hierarchy level

[v-srekom]

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?

[v-diye-msft · Accepted solution]

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/ 

https://community.powerbi.com/t5/Desktop/Calculating-measures-based-on-different-hierarchy-levels/td...

[Greg_Deckler]

@v-srekom - See if MM3TR&R helps: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

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.

[pranit828]

HI @v-srekom 

Please also share your sample input and if yo would like to add any more detail.

[v-srekom]

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.