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

[cboelterGNH]

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.

[Anonymous]

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

[aduguid · Accepted solution]

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