Forum Discussion
Matrix Show Different Hierarchy Levels
Use ISINSCOPE to determine check whether a column is the level in a hierarchy of levels. In the image below, Gross Profit is already expanded but the next level in the hierarchy is not visible. A condition was applied to return the second level as blank when it is in scope and the first level is Gross Profit.
IF ( ISINSCOPE ( tbl[hierarchy2] ) && SELECTEDVALUE ( tbl[hierarchy1] ) = "Gross Profit", blank, [pnl measure] )
Use ISINSCOPE to determine check whether a column is the level in a hierarchy of levels. In the image below, Gross Profit is already expanded but the next level in the hierarchy is not visible. A condition was applied to return the second level as blank when it is in scope and the first level is Gross Profit.
IF (
ISINSCOPE ( tbl[hierarchy2] )
&& SELECTEDVALUE ( tbl[hierarchy1] ) = "Gross Profit",
blank,
[pnl measure]
)
I see of couple of challenges with your screenshot:
1. the expand/collapse button still shows for the Gross fit even tho it doesnt have children. It will be ideal if it would not show it, just as it was a leaf on the hierarchy.
2. It also shows in bold instead to regular font like the leaf.That said, I have tried making it work, however I was unlucky, here is the problem I'm having.
Assuming you meant to use the formula as filter right, I have created two measures, where "sectionFieldInScope" is simply for debugging it:
sectionFieldInScope =SWITCH(TRUE(),ISINSCOPE(Clauses2[Level2]), "Level2",ISINSCOPE(Clauses2[Level1]), "Level1",ISINSCOPE(Clauses2[Section]), "Section",ISINSCOPE(Clauses2[Country]), "Country","None")showRow =IF(ISINSCOPE(Clauses2[Level2]) && SELECTEDVALUE ( Clauses2[Section] ) = "Scope", "no","yes")
The issue is that when I add the filter it removes the full scope row, which means also removes the section row:
The data underneath looks like this:Country Section Level1 Level2 Belgium Scope Banana and Plantain Crops Belgium Benefits Legal and Jurisdiction Immunities Some legal benefit for the institution Belgium Benefits Legal and Jurisdiction Immunities Office is inviolable
