topic Re: How to show all Rows in matrix Even if there is no data in DAX Commands and Tips

How to show all Rows in matrix Even if there is no data

cboelterGNH — Thu, 20 Jun 2024 21:00:58 GMT

Hi,

I have a client-request that wants to show all rows of a Dimension Table (In this case all of the specific Departments) even when there are no values.

Example: a filter is set for a weekend and only one department works that weekend, so all departments will show 0 except one.

There are 44 Departments Total. The Dax is:

Example Hours = SUM(Fact_Timecards[Hours]) + 0

Below is what we don't want to happen (removing departments), the code field is the one from the Dimension Table. (I'm also using the show data with no values setting on the Rows [Code], clearly is not working).

Here is a look at the Model for Reference

Though when I disactivate the relationship, I get the familiar repeated-value.

Any suggestions is welome, thank you.

Re: How to show all Rows in matrix Even if there is no data

aduguid — Thu, 20 Jun 2024 22:47:20 GMT

If you change the relationship to "Both" from Fact_Timecards to DepartmentCodes, it should work.

Re: How to show all Rows in matrix Even if there is no data

Anonymous — Mon, 24 Jun 2024 01:59:16 GMT

Hi cboelterGNH ,

You can follow the steps below to get it:

1. Delete the relationship between the table 'Fact_Timecards' and 'DepartmentCodes'

2. Create a measure as below

Measure_ExampleHours = VAR _code = SELECTEDVALUE ( 'DepartmentCodes'[Code] ) RETURN CALCULATE ( SUM ( 'Fact_Timecards'[Example Hours] ), FILTER ( 'Fact_Timecards', 'Fact_Timecards'[Home Department-9999XX] = _code ) )

3. Put the above measure onto the Values field to replace the original one

Best Regards