Matrix with different levels of hierarchy in row and corresponding values

Anonymous — Mon, 03 May 2021 11:19:10 GMT

Hi all,

I have requirement to display matrix where row basically KPI list, will have different levels of hierarchy to show and corresponding values for each KPI- which has different logic for each KPI.
I have 4 different levels - KPI name -> Level 1-> Level 2 -> Level 3

To get this view in matrix I have created table by just inserting values in every column based on requirement.

The requirement is to show corresponding values for each KPI including all levels of hierachy .
Each KPI has its own logic to display values. I have created measure to calculate values for each KPI but unable to get the same in the requested matrix view. I tried using switch statement but got stuck at some point as how to include logic for each level1,level2 and level3 to get the correct data in matrix view.

ALSO, how to get rid of the rows where I don't have further level or when its blank. It shouldn't have + expand symbol though in matrix . For example in my list, KPI 1 and KPI 4.

Measure Logic = SWITCH(MAX('KPI Table'[KPI name]),
"KPI 1",[KPI 1],
"KPI 2",[KPI 2],
"KPI 3",[KPI 3],
"KPI 4",[KPI 4],
200)

Screenshot attached for table and matrix view

Any help will be much appreciated. Thanks in advance.

Re: Matrix with different levels of hierarchy in row and corresponding values

amitchandak — Mon, 03 May 2021 11:57:19 GMT

Anonymous , Not very clear, but isinscope can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

topic Matrix with different levels of hierarchy in row and corresponding values in DAX Commands and Tips

Matrix with different levels of hierarchy in row and corresponding values

Re: Matrix with different levels of hierarchy in row and corresponding values