How to show all names in a matrix & slicers even if they have empty values?

[JDonnellan]

Hi everyone, I am just wondering for the following matrix on this report is there a way that I would be able to show every single name on the Leave Request even those that do not have a leave request for that week and would just be blank?

Another issue is it  only shows the department, location and job titles of those who have a leave request booked so ideally the solution would be one that also shows every single option in them slicers and every individual name in the Leave Request matrix whether they have or don't have a request booked.

Here is how it currently looks:

Here is the data for the matrix:

I would appreciate any help regarding this or any solution methods. Thank you!

[danextian]

Hi @JDonnellan 

Create a separate dimension table for the employee, location, id and job title. Relate that to your fact table and use that column instead. Click down arrow on a field in matrix and ensure that Show items with no data is checked.

Without using a separate dimensions table, only employees with records within the selected range will be displayed, as illustrated in the screenshot below. This occurs because it is not possible to assign a value to a non-existent row, such as the row of an employee outside the selected range even if "Show items with no data" is enabled.

In contrast, the example below uses a column from a separate dimension table. This column is not directly influenced by the date table.

Please find the attached sample PBIX file.

It also includes an example of how to plot employee leave data on the dates within the specified start and end leave dates.

[JDonnellan]

Hi there @danextian , thanks for the quick reply. 

Just to clarify, do you mean duplicate the leave viewer table I have in transform data but only keep the employee, location, id and job title.

Then create a relationship with the original table through the Name column (one to one)

And then use this new Name as a row in the matrix and have Show items with no data selected and it should show all indivduals now?

[danextian]

In the query editor, duplicate the leave table. Keep only the employee name/ID, location, and job title in this duplicated table, and name it 'Employees' or 'Dim_Employees' based on your preference. For the original table, retain only the employee ID, the date the leaves were filed, and the start and end dates of the leaves. All other information can be referenced from the employee table. Ensure you establish a one-to-many relationship between the tables, as a one-to-one relationship inherently creates a bi-directional relationship. The relationship direction should be from the employee table to the leaves table. Please refer to the sample PBIX in my initial reply. While I used DAX to create the employee table, you can also accomplish this using M.

[JDonnellan]

I appreicate your help. I am encountering this error unfortunately 

[Anonymous]

Hi ,

Based on the information, try to create a bridge table with only unique records then connect both the tables with this newly bridge table.

You can view the following documents to learn more information.

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Solved: Column Item no in Table Product contains a duplica... - Microsoft Fabric Community

Solved: Getting "not allowed for columns on the one side o... - Microsoft Fabric Community

Solved: Refresh error: "...contains duplicate value...and ... - Microsoft Fabric Community

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Chewdata]

• Hey!
  
  You can try the following:
  
  Right-click on the field that you want to show, even if it has no values.
  Click on Show items iwth no Data

It will then show all available values for that field, even if it does not have a corresponding value.

Hopefully this helps. If so, please Kudo and accept as solution, so other users can find the answer more quickly!