Forum Discussion

un97's avatar
un97
Regular Visitor
3 years ago

Hierarchy skip (not hide) blank rows

Hi, my data looks something like the table below. With Lvl1 being the highest in the heirarchy and Lvl5 being the lowest.  

 

TABLE:

IDLvl1Lvl2Lvl3Lvl4Lvl5
1CostsFinancial ExpensesFees Bank charges
2CostsOther ExpensesOther ServicesIT ServicesSecurity
3CostsPersonel ExpensesHiring Costs Recruitment
4CostsAdmin ExpensesProperty Insurance Insurance Other
5CostsFinancial ExpensesTransport Transport

 

I also have a DAX measure: COUNTROWS(TABLE)

 

I need to create a matrix that shows the heirarchy of these levels and how many rows each level has. If I only add all the levels to Rows of the matrix, I get a blank row for the blanks in Lvl4, but I can still expand that row to see the values from Lvl5. 

 

What I would need to create is a DAX measure that skips (not hides) the blanks. Meaning, if there is no value in Lvl4, it should show Lvl5 instead.

So, for ID 1, I should have:
Costs

   Financial Expenses

      Fees

         Bank charges

 

and for ID 2 I should have:

Costs

   Other Expenses

      Other Services

         IT Services

            Security

 

I created the following DAX measure (below), but this only hides the rows where Lvl4 is blank, so the hierarchy ends at Lvl3, without displaying the Lvl5 values. 

 

Countrows Table =
SWITCH(TRUE(),
AND(ISINSCOPE(TABLE[Lvl1]), ISBLANK(VALUES(TABLE[Lvl1]))), BLANK(),
AND(ISINSCOPE(TABLE[Lvl2]), ISBLANK(VALUES(TABLE[Lvl2]))), BLANK(),
AND(ISINSCOPE(TABLE[Lvl3]), ISBLANK(VALUES(TABLE[Lvl3]))), BLANK(),
AND(ISINSCOPE(TABLE[Lvl4]), ISBLANK(VALUES(TABLE[Lvl4]))), BLANK(),
AND(ISINSCOPE(TABLE[Lvl5]), ISBLANK(VALUES(TABLE[Lvl5]))), BLANK(),
COUNTROWS(TABLE))
 
How can I adjust my measure or create a new one to get the desired result? 
 
Thanks!
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hey un97, have you tried something like this?

     

    CountRowsTable =
    VAR SelectedLvl4 = VALUES(TABLE[Lvl4])
    VAR SelectedLvl5 = VALUES(TABLE[Lvl5])
    VAR Count = COUNTROWS(TABLE)
    RETURN
    SWITCH(
    TRUE(),
    ISINSCOPE(TABLE[Lvl5]) && ISBLANK(SelectedLvl4), CONCATENATEX(SelectedLvl5, TABLE[Lvl5], " > "),
    ISINSCOPE(TABLE[Lvl4]) && ISBLANK(SelectedLvl5), CONCATENATEX(SelectedLvl4, TABLE[Lvl4], " > "),
    ISINSCOPE(TABLE[Lvl3]), CONCATENATEX(VALUES(TABLE[Lvl3]), TABLE[Lvl3], " > "),
    ISINSCOPE(TABLE[Lvl2]), CONCATENATEX(VALUES(TABLE[Lvl2]), TABLE[Lvl2], " > "),
    ISINSCOPE(TABLE[Lvl1]), CONCATENATEX(VALUES(TABLE[Lvl1]), TABLE[Lvl1], " > "),
    BLANK()
    ) & Count

    • un97's avatar
      un97
      Regular Visitor
      3 years ago

      Hi devindupree01, unfortunately this measure didn't work. 

       

      The result I got with the measure you provided is this: