Showing null/blank values as 0 in matrix causing all dates to show

[Sm95]

Hi All,

I have a requirement to show the blank values in my matrix as 0 with my date in column shelf of matrix. But when I use my created measure in the matrix, it starts showing all the dates (which are not even part of my data).

I have tried various suggestions provided to use a different date table, link it with primary table and use it in the matrix. But I am still getting the same result (i.e. all months). Please help with your inputs or alternative suggestions. Below is my scenario in detail.

1) Original Output:

2) Created measure to show as 0:

Sales_ZN = SUM('Table'[Sales])+0

3) Result with Sales_ZN measure: (showing all the months)

Getting the same output when creating a separate date table, linking it with original primary table and using the date field in matrix.

4) Expected Output:

Thanks!

[Sm95]

Hi @v-jayw-msft , 

Thanks a lot for the suggestion. This is working as expected. Just a question, could you please explain why have you used MAX function in CALCULATE? I got the expected values using SUM as well, instead of MAX.

CALCULATE (
            MAX ( 'Table'[Sales] ),
            ALLEXCEPT ( 'Table', 'Table'[Year], 'Table'[Month] )
        )

[v-jayw-msft]

Hi @Sm95 ,

Yes, you could use SUM() as well.

If the all the value in that column is blank, both max and sum will be blank.

Best Regards,

Jay

[v-jayw-msft · Accepted solution]

Hi @Sm95 ,

Try this formula:

Sales_ZN =
IF (
    ISBLANK (
        CALCULATE (
            MAX ( 'Table'[Sales] ),
            ALLEXCEPT ( 'Table', 'Table'[Year], 'Table'[Month] )
        )
    ),
    BLANK (),
    SUM ( 'Table'[Sales] ) + 0
)

Best Regards,

Jay