How to hide/remove Blank Rows in Matrix and show the values

[Anonymous]

Hi,

I have employees table where we have level 2 to level 9 leaders. In matrix we have taken all the level leaders and employee in the rows. I want to remove the blank rows and roll them up.

If the employee is level 5, then matrix should display level 2, 3, 4 leaders and then employee name without blank rows

If the employee is level 6, then matrix should display level 2, 3, 4, 5 leaders and then employee name without blank rows

I have kept the leader levels next to their name.

In the above picture Employee is at level 5, so from level 5-9 it will be blank rows, we want hide blank rows and roll up the employee name under level 4 leader.

Employee is at level 6, so from level 6-9 it will be blank rows, we want hide blank rows and roll up the employee name under level 5 leader.

[v-xiaocliu-msft · Accepted solution]

Hi @Anonymous ,

I modified the data structure and made a demo for you.

Measure = 
var _userName = SWITCH(TRUE(),
ISINSCOPE('Table3'[Level 9 Leader]),MAX('Table3'[Level 9 Leader]),
ISINSCOPE('Table3'[Level 8 Leader]),MAX('Table3'[Level 8 Leader]),
ISINSCOPE('Table3'[Level 7 Leader]),MAX('Table3'[Level 7 Leader]),
ISINSCOPE('Table3'[Level 6 Leader]),MAX('Table3'[Level 6 Leader]),
ISINSCOPE('Table3'[Level 5 Leader]),MAX('Table3'[Level 5 Leader]),
ISINSCOPE('Table3'[Level 4 Leader]),MAX('Table3'[Level 4 Leader]),
ISINSCOPE('Table3'[Level 3 Leader]),MAX('Table3'[Level 3 Leader]),
ISINSCOPE('Table3'[Level 2 Leader]),MAX('Table3'[Level 2 Leader]))
var _totalAssigned = IF(NOT(ISBLANK( _userName)),MAXX( FILTER(ALL(Table1),[Employee Name]=_userName),[Total Assigned])  ,BLANK())
RETURN _totalAssigned

Best Regards,

Wearsky

[v-xiaocliu-msft]

Hi @Anonymous ,

I created the data table manually. 

Please try the solution provided by @elitesmitpatel . 

I think it will help you to create the columns dynamically.

Best Regards,

Wearsky

[v-xiaocliu-msft · Accepted solution]

Hi @Anonymous ,

I modified the data structure and made a demo for you.

Measure = 
var _userName = SWITCH(TRUE(),
ISINSCOPE('Table3'[Level 9 Leader]),MAX('Table3'[Level 9 Leader]),
ISINSCOPE('Table3'[Level 8 Leader]),MAX('Table3'[Level 8 Leader]),
ISINSCOPE('Table3'[Level 7 Leader]),MAX('Table3'[Level 7 Leader]),
ISINSCOPE('Table3'[Level 6 Leader]),MAX('Table3'[Level 6 Leader]),
ISINSCOPE('Table3'[Level 5 Leader]),MAX('Table3'[Level 5 Leader]),
ISINSCOPE('Table3'[Level 4 Leader]),MAX('Table3'[Level 4 Leader]),
ISINSCOPE('Table3'[Level 3 Leader]),MAX('Table3'[Level 3 Leader]),
ISINSCOPE('Table3'[Level 2 Leader]),MAX('Table3'[Level 2 Leader]))
var _totalAssigned = IF(NOT(ISBLANK( _userName)),MAXX( FILTER(ALL(Table1),[Employee Name]=_userName),[Total Assigned])  ,BLANK())
RETURN _totalAssigned

Best Regards,

Wearsky

[Anonymous]

@v-xiaocliu-msft 

Hi,

Thanks for the measure. 

This is a sample data given for the better understanding of the scenario. Our Original employees table has 66K employees. 

If an employee is at level 4 in the org, then we have values(names) from level 1-3 and from level 4-9 it is blank

If an employee is at level 5 in the org, then we have values(names) from level 1-4 and from level 5-9 it is blank

If an employee is at level 6 in the org, then we have values(names) from level 1-5 and from level 6-9 it is blank

How to get the employee name dynamically in his orginal level if the table has 66k records? Do you have any dax to get that? was it done manually in the above excel since it has only 11 reacords?

[Anonymous]

@elitesmitpatel I couldnt upload this sample data pbix. But i have took the screenshot of data from the sample file and pasted here. Check it

[elitesmitpatel · Accepted solution]

Hi @Anonymous 
I have make some changes shown below as this is the standard form of parent child herirachy.

The Requested output 

For better reference please refer below pbix link -->
How to hide/remove Blank Rows in Matrix and show the values Solved 

If it helps please Accept it as solution

[Jalil239]

Try This one if not let me know 

[Anonymous]

@Jalil429: this doenst work

[dharmendars007]

Hello @Anonymous 

Please select the visual and the rows attribute and filter out Blanks using in the filter panel.

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Anonymous]

@dharmendars007 No this doesn't work for my issue. 

[elitesmitpatel]

Please share the pbix file with dummy data