Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

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.

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

10 Replies

  • dharmendars007's avatar
    dharmendars007
    Memorable Member
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      @Jalil429: this doenst work

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      Anonymous 

      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's avatar
    Anonymous
    Not applicable
    1 year ago

    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