Matrix Show Different Hierarchy Levels

[david_avgarcia]

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)

[david_avgarcia]

Hidding that subtotals label is also possible. I found the setting which allows to configure both, Text Color and Background label for that row:

[david_avgarcia]

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

[v-nmadadi-msft]

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 you

[david_avgarcia]

if 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!

[david_avgarcia]

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!

[v-nmadadi-msft]

Hi @david_avgarcia  

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

[david_avgarcia]

@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? 

[danextian · Accepted solution]

Hi @david_avgarcia 

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]
)

[david_avgarcia]

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