topic Re: Pulling a value from a lower hierarchy in a matrix in DAX Commands and Tips

Pulling a value from a lower hierarchy in a matrix

akoren2 — Wed, 12 Jun 2024 20:25:11 GMT

Hi All!

I have a matrix with 4 levels. There are possible values on each one of those hierarchy levels. I need to display a flag on the top most hierarchy level (level 1) if there is a value that exists on any of the four levels. Does anyone have any tips on how to accomplish that?

Thank you!

Re: Pulling a value from a lower hierarchy in a matrix

ExcelMonke — Wed, 12 Jun 2024 20:52:02 GMT

Hello,

You can consider using a mix of Variables and IF statements. Consider:

MEASURE = VAR _Level1 = IF ( ISBLANK ( 'Table'[Level1] ), 0, 1 ) //You can tweak the "ISBLANK" statement depending on how you are determining value for the level VAR _Level2 = IF ( ISBLANK ( 'Table'[Level2] ), 0, 1 ) VAR _Level3 = IF ( ISBLANK ( 'Table'[Level3] ), 0, 1 ) VAR _Level4 = IF ( ISBLANK ( 'Table'[Level4] ), 0, 1 ) RETURN IF ( ( _Level1 + _Level2 + _Level3 + _Level4 ) > 0, "Flag", "" )

Re: Pulling a value from a lower hierarchy in a matrix

akoren2 — Wed, 12 Jun 2024 20:59:43 GMT

Thanks for your reply!
I have tried something similar, but here it is with you structure. It still only displays the flag when the measure exists on that specific level

Commentary Flag =

VAR _Level1 =

IF ( ISINSCOPE(Rpt_Layout[LVL_1]) && NOT isblank([Commentary]) , 1, 0 )

VAR _Level2 =

IF ( ISINSCOPE(Rpt_Layout[LVL_2]) && NOT isblank([Commentary]) , 1, 0 )

VAR _Level3 =

IF ( ISINSCOPE(Rpt_Layout[LVL_3_Sort]) && NOT isblank([Commentary]) , 1, 0 )

VAR _Level4 =

IF ( ISINSCOPE(Rpt_Layout[LVL_4]) && NOT isblank([Commentary]) , 1, 0 )

RETURN

IF ( ( _Level1 + _Level2 + _Level3 + _Level4 ) > 0, "Flag", "" )

Re: Pulling a value from a lower hierarchy in a matrix

ExcelMonke — Thu, 13 Jun 2024 15:00:17 GMT

Could you show a screenshot of what the result looks like now?