Forum Discussion
Matrix Show Different Hierarchy Levels
I'm using a matrix to the display the following table:
Country | Section | Header | Clause
Belgium | Scope | | This is the scope
Belgium | Benefits | Tax | Exemption from all direct taxes
I have drag Country, Section, Header and Clause Fields, but it always creates and empty row when showing the header field for the scope:
[-] Belgium
[-] Scope
[-]
This is the scope
[-] Benefits
[-] Tax
[-] Exemption from all direct taxes
I have tried creating computed value which shows the clause if the header is blank:
ComputedLevel2 =IF(ISBLANK([Header]),[Clause],[Header])
But it simply moves the empty row one down and shows my clause the expand collapse as if it has children:
[-] Belgium
[-] Scope
[-] This is the scope
[-] Benefits
[-] Tax
[-] Exemption from all direct taxes
What i want is for scope to remove empty levels and show only 1 level (clause) in the hierarchy but for benefits to show the two (Header and clause)
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] )
9 Replies
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
Hidding that subtotals label is also possible. I found the setting which allows to configure both, Text Color and Background label for that row:
danextian , I think your solution could work. Could you please confirm how did you manage to hide the blank rows? It was not setting that measure as a filter right?
Ok, I got showing like danextian . Instead of using a filter with that formula, that formula will be a measure and the measure should be on the values. Thank you!
May I check if this issue has been resolved by the suggestion provided by danextian ? If not, Please feel free to contact us if you have any further questions.
Thank you
To hide the extra rows of hierarchy as danextian pointed out, please note that is not about setting filters but about using the the showRow measure in the matrix's values.
An important thing to notice is that this only works if subtotals is enabled, otherwise the full row is removed.The issue now is how do you hide the label "Total", as it does not make sense here. I've tried covering it with a rectangle but the vertical position varies depending on the active filters. I could not find and option to set the color only for that last Total row, so I've changed the text to something that is less obvious
Hi david_avgarcia ,
Please go to Format Visual,
find Row Subtotals and Column Subtotals setting over there and disable them
I hope this information helps. Please do let us know if you have any further queries.
Thank youif you disable the totals it will hide the full row (full branch of the tree), not just the child empty hierarchy levels. But you can do that trick with the colors as indicated aboved, thanks!
