Handling Stepped Matrix and Displaying values based on Current Stepped Row Level

[Anonymous]

Hello!

I am currently trying to build a matrix visual that would incorporate Hierarchy with a functionality of choosing displayed value based on currently Expanded Row (Expanding/Collapsing changes the calculated column from which we take the value):

So, for example:

So basically I'm looking for a way to create a measure, that verifies on which ROWS level I am:

And then takes correct calculated column (PRJ/PGM/INI).

Do you think thats possible?

Attaching demo values (can't upload .pbix somehow).

and visual:

[Anonymous]

Excellent, works like a charm!

There should be a cheatsheet with tricks like this (SWITCH; RETURN) as I never used it so cleverly.

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create two measures as below, please find the details in the attachment:

RAG = 
VAR _iniselected =
    ISINSCOPE ( 'Table'[Initiative] )
VAR _proselected =
    ISINSCOPE ( 'Table'[Program] )
VAR _inirag =
    CALCULATE (
        MAX ( 'Table'[INI.RAG] ),
        ALLEXCEPT ( 'Table', 'Table'[Initiative] )
    )
VAR _prorag =
    CALCULATE (
        MAX ( 'Table'[PGM.RAG] ),
        ALLEXCEPT ( 'Table', 'Table'[Program] )
    )
RETURN
    SWITCH ( TRUE (), _proselected, _prorag, _iniselected, _inirag )

Status = 
VAR _iniselected =
    ISINSCOPE ( 'Table'[Initiative] )
VAR _proselected =
    ISINSCOPE ( 'Table'[Program] )
VAR _inistatus =
    CALCULATE (
        MAX ( 'Table'[INI.Status] ),
        ALLEXCEPT ( 'Table', 'Table'[Initiative] )
    )
VAR _prostatus =
    CALCULATE (
        MAX ( 'Table'[PGM.Status] ),
        ALLEXCEPT ( 'Table', 'Table'[Program] )
    )
RETURN
    SWITCH ( TRUE (), _proselected, _prostatus, _iniselected, _inistatus )

Best Regards