Forum Discussion
Power BI Matrix Last Node (in Rows) should display on all levels (of rows) as and when expanded
Hi Alpha0904 ,
Thanks for checking and for the clarification, as u said my previous example used columns mainly to demonstrate the value behavior but that is not required for your scenario, from what you described you want the last level Types to be visible across all levels in the row hierarchy when expanded, and this is more about how the matrix displays hierarchy than just measure logic because Power BI does not natively repeat leaf level values at higher levels, the PBIX I shared was focused on value behavior so it may not fully match your requirement, achieving this would need some additional modeling or DAX approach rather than just visual settings, if you can share a small sample of your hierarchy It would be helpful.
Hii Alpha0904
It can be achieved using a DAX measure that identifies the current hierarchy level and dynamically returns the last child node’s value for parent levels.
The approach uses ISINSCOPE to detect the level and CALCULATE with MAX to fetch the last node value based on a properly sorted column.
Create Last Node Measure:-
Last Node Value =
CALCULATE(
MAX('Table'[Value]),
FILTER(
ALL('Table'),
'Table'[Type] =
MAXX(
FILTER(
ALL('Table'),
'Table'[Row2] = MAX('Table'[Row2])
),
'Table'[Type]
)
)
)Show at All Levels:-
Final Value =
VAR IsLowestLevel = ISINSCOPE('Table'[Type])
RETURN
IF(
IsLowestLevel,
SELECTEDVALUE('Table'[Value]),
[Last Node Value]
)To correctly identify the “last” node, define a custom sort order:-
SortOrder =
SWITCH(
'Table'[Type],
"Type1",1,
"Type2",2,
"Type3",3
)