Hi, my data looks something like the table below. With Lvl1 being the highest in the heirarchy and Lvl5 being the lowest.
TABLE:
| ID | Lvl1 | Lvl2 | Lvl3 | Lvl4 | Lvl5 |
| 1 | Costs | Financial Expenses | Fees | | Bank charges |
| 2 | Costs | Other Expenses | Other Services | IT Services | Security |
| 3 | Costs | Personel Expenses | Hiring Costs | | Recruitment |
| 4 | Costs | Admin Expenses | Property Insurance | | Insurance Other |
| 5 | Costs | Financial Expenses | Transport | | 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!
