Forum Discussion
Handling Stepped Matrix and Displaying values based on Current Stepped Row Level
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:
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
2 Replies
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