Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
akoren2
Frequent Visitor

Pulling a value from a lower hierarchy in a matrix

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!

3 REPLIES 3
ExcelMonke
Solution Supplier
Solution Supplier

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

ExcelMonke
Solution Supplier
Solution Supplier

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", "" )

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", "" )

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.