Forum Discussion
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
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
- MasonMASuper User
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.
- PaulKraemerAdvocate 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- MasonMASuper 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
- Nasif_AzamSuper User
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:
-
Insert a Table visual instead of a Matrix.
-
Add the following fields:
- Company
- EmployeeLabel (from the new column)
- Count of Ticket Number
-
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- PaulKraemerAdvocate II
Thank you MasonMA and Nasif - This works great for me.
I appreciate your help!
Best regards,
Paul
-