Replace Blanks with 0

[Anonymous]

Hello,

I have a long if statement being used as a count in a matrix figure. I would very much like the blanks to be zeros. Everything I have tried has come up short. Thanks in advance for any assistance.

 

[v-yulgu-msft]

Hi @Anonymous,

As BraneyBI said, "The problem is that for each intersection of your matrix where you want to show the 0, there are NO ROWS in your table with the correct filter context.". So, the workaround I suggest is inserting rows for each ACCT_STATUS per date range.

After adding the calculated columns with above long if statement, please create calculated tables as below:

Not-Worked_1 =
CROSSJOIN (
    VALUES ( 'Not-Worked'[Acct Status] ),
    VALUES ( 'Not-Worked'[Days in Status] )
)

Not-Worked_2 =
UNION (
    'Not-Worked',
    SELECTCOLUMNS (
        'Not-Worked_1',
        "Acct Status", 'Not-Worked_1'[Acct Status],
        "Days in status", 'Not-Worked_1'[Days in Status],
        "Value", 0
    )
)

Drag corresponding fields from 'Not-Worked_2' to Matrix visual.

Best regards,

Yuliana Gu

[Anonymous]

Hey @v-yulgu-msft

Thanks for the reply. When I attempt what you have suggested I get the following error.

Do you see any errors in my formula or do you have a guess as to why this error is occuring?

Thanks

[danextian]

Hi @Anonymous

Does actual source really look like that? Or is it something like below?

Acct Status	Days Range	Measure
A	0-1 days	45
B	2-3 days	42
C	4 - 10 days	36
D	10-15 days	12
E	0-1 days	37
A	0-1 days	1
B	2-3 days	11
C	10-15 days	42
D	10-15 days	38
E	2-3 days	7

[Anonymous]

Hey @danextian

Yes. My data has many rows of the ACCT_STATUS their corresponding Days in Status value.

[Vvelarde]

@Anonymous

Hi, I reccomended to use DAX Formater to format your code.

http://www.daxformatter.com/

Will be more easy to understand it.

Regards

Victor

[BraneyBI]

This one is tricky.  The problem is that for each intersection of your matrix where you want to show the 0, there are NO ROWS in your table with the correct filter context.  I have a hack which will work, but requires a little formatting trickery. 

Based on your picture below, it appears your table is called 'Not-Worked'

In a Different table (I usually use Date), create a measure

      ShowZero = Calculate(.001, ALL('Not-Worked'))

It is important that you DO NOT PUT IT IN YOUR 'Not-Worked' table.

Add this value to your original measure 

     OriginalMeasure = IF(REALLY LONG DAX HERE...) + ShowZero

This will add the really small number to your measure, which when formatted without decimals, will show a ZERO!  This works because of the ALL() function which removes any filter context coming from the  'Not-Worked' table.

[Anonymous]

Hey @BraneyBI

When I attempted your work-around I got the following error

Here is my ShowZero Measure

Any thoughts?

Thank you