Default Matrix Visualization to Expanded, and disable collapsing?

[PaulKraemer]

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 

[Nasif_Azam]

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]

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

I appreciate your help!

Best regards,
Paul

[MasonMA]

@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]

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 · Accepted solution]

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