Forum Discussion

PaulKraemer's avatar
PaulKraemer
Advocate II
10 months ago
Solved

Default Matrix Visualization to Expanded, and disable collapsing?

Hi,

In Power BI, I have a Sales Table and an Employees Table with fields as listed below:

 

Sales

Company

Employee ID

Ticket Number

U_Emp_ID

 

Employees

Company

Employee ID

Employee Name

U_Emp_ID

 

I have defined a one-to-many relationship from the Employees table to the Sales table that is based on the U_Emp_ID field.  I have created a Matrix visualization that breaks down Sales data first by Company and then by Employee.  I prefer to show both Employee ID and Employee Name for each Employee, so I have defined three Rows in my visualization - first Company, then Employee ID, and finally Employee Name.  For Values, I show the Count of Ticket Number (from the Sales Table).  I set the Layout for my visualization to Tabular and I configured my Row subtotals so that a subtotal is shown for each company and a grand total is shown, but a subtotal is not shown for each individual employee.  This allows me to produce a view like I've pasted below, which is exactly what I want ....  

 

... My only complaint is that to make it look like this, I had to click the "+" for each Employee ID to expand the grouping before I saw the Employee names.  Before I clicked on the "+", it looked like the image below on the left.  After I clicked two of the "+" signs, it looked like the image below on the right.  I wasn't until I clicked all the "+" signs that it looked like the image above.  

 

 

There is no circumstance when I do not want to see the Employee Names.  Is there I way I can make this visualization so that the default is for every Employee ID grouping to be expanded?  If this is a possible, is there also way I can disable the option to collapse the Employee ID grouping?

 

Here is a link to the .pbix file I am working with.

 

LookingUpEmployeeNameInMatrix_01.pbix

 

Any suggestions will be greatly appreciated.

 

Thanks in advance,
Paul 

 

 

 

     

 

 

  • MasonMA's avatar
    MasonMA
    10 months ago

    Hi Paul,

     

    The alternative way would be creating a new column to combine 'Employee ID' and 'Employee Name' in 'Employees' table with below DAX, 

    ID Employee Name =Employees[Employee ID] & " - " & Employees[Employee Name]

    Then keep the stepped layout enabled, but use this new column instead on your Matrix

     

     

     

5 Replies

  • PaulKraemer 

     

    Hi, would this be the view you were looking to have? If it is you can disable the '+/- icon' for stepped layout under Text, Row headers. 

     

    • PaulKraemer's avatar
      PaulKraemer
      Advocate II

      Hi MasonMA -

       

      Thank you for your reply.  Ideally, I would like users to have the ability to expand and collapse at the top (company) level, so that they can achieve any of the following views (A, B, C):

       

       

      What I'd like to do is avoid the possibility of a view like (D) below, where an Employee ID is displayed but the Employee Name is not :

       

       

      ... The option you suggested to turn off the +/- icons seems to apply to all grouping levels.  Ideally, I would like the +/- icons to be available at the top level (Company) but not at the second level (Employee ID).

       

      If this isn't possible, I am open to suggestions if there is an alternate way to accomplish the same objective.

       

      I appreciate your help.

       

      Best regards,
      Paul

       

       

       

      • MasonMA's avatar
        MasonMA
        Super User

        Hi Paul,

         

        The alternative way would be creating a new column to combine 'Employee ID' and 'Employee Name' in 'Employees' table with below DAX, 

        ID Employee Name =Employees[Employee ID] & " - " & Employees[Employee Name]

        Then keep the stepped layout enabled, but use this new column instead on your Matrix

         

         

         

  • Hey PaulKraemer ,

    There are 3 way I know. Please go through that and let me know the feedback.

     

    Option-1:

    For the following requirement you need a custom column which is insert into the Row level instead of Employee ID and Employee Name.
    Custom Column:

     

    Martix:

     

     

    Option-2: 

    If you do not need hierarchical grouping:

    1. Insert a Table visual instead of a Matrix.

    2. Add the following fields:

      • Company
      • EmployeeLabel (from the new column)
      • Count of Ticket Number
    3. Format as needed (sorting, totals, etc.).

     

    Option-3: While you cannot disable the expand/collapse icons in Matrix, using the concatenated column removes the need for them entirely.


    Please see the Power BI file I attached.


    Best Regards,
    Nasif Azam

    • PaulKraemer's avatar
      PaulKraemer
      Advocate II

      Thank you MasonMA and Nasif - This works great for me.

       

      I appreciate your help!

       

      Best regards,
      Paul