Replace the blank values of my matrix (driven by an if statement) 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.

 

[MFelix]

Hi @Anonymous,

First of all you have several issues with your formula that make it more complex to read:

Instead of using several IF within IF you should use the SWITCH function check this link, this is basically a IF on steroids and helps you get your DAX measure in a better way.

I would also consider instead of making a huge formula like you have make several smaller ones and then combine into a single one.

Second of all you should use https://www.daxformatter.com/ to format your DAX measure in a better way to be more readable this helps you formatting your code.

For the result try to create a new measure where you do the following:

Days in status = IF ( [Days in Status (Base Work)] = Blank() ; 0 ; [Days in Status (Base Work)])

Use this formula on your report to give the expected result.

Regards,

MFelix

[Anonymous]

@MFelix, thanks for the reply

Here is the if statement in the easier to read format.

Days in Status (Base Work) =
IF (
    'NOT-WORKED'[- Status Date] <= 0 + CALCULATE (
                AVERAGE ( 'NOT-WORKED'[Base Range] ),
                ALL ( Vlookup[Status] )
            )
        && 'NOT-WORKED'[- Status Date] >= -10 + CALCULATE (
                    AVERAGE ( 'NOT-WORKED'[Base Range] ),
                    ALL ( Vlookup[Rehab Status] )
                ),
    "Upcoming",
    IF (
        'NOT-WORKED'[- Status Date] > 0 + CALCULATE (
                    AVERAGE ( 'NOT-WORKED'[Base Range] ),
                    ALL ( Vlookup[Rehab Status] )
                )
            && 'NOT-WORKED'[- Status Date] <= 15 + CALCULATE (
                        AVERAGE ( 'NOT-WORKED'[Base Range] ),
                        ALL ( Vlookup[Status] )
                    ),
        "0 to 15 days",
        IF (
            'NOT-WORKED'[- Status Date] > 15 + CALCULATE (
                        AVERAGE ( 'NOT-WORKED'[Base Range] ),
                        ALL ( Vlookup[Status] )
                    )
                && 'NOT-WORKED'[- Status Date] <= 30 + CALCULATE (
                            AVERAGE ( 'NOT-WORKED'[Base Range] ),
                            ALL ( Vlookup[Status] )
                        ),
            "16 to 30 days",
            IF (
                'NOT-WORKED'[- Status Date] > 30 + CALCULATE (
                            AVERAGE ( 'NOT-WORKED'[Base Range] ),
                            ALL ( Vlookup[Status] )
                        )
                    && 'NOT-WORKED'[- Status Date] <= 45 + CALCULATE (
                                AVERAGE ( 'NOT-WORKED'[Base Range] ),
                                ALL ( Vlookup[Status] )
                            ),
                "31 to 45 days",
                IF (
                    'NOT-WORKED'[- Status Date] > 45 + CALCULATE (
                                AVERAGE ( 'NOT-WORKED'[Base Range] ),
                                ALL ( Vlookup[Status] )
                            ),
                    "46+ days",
                    "x"
                )
            )
        )
    )
)

The Switch function isnt applicable because it relies on a constant value and mine are ranges.

The formula you supplied doesnt work because there are no blank values in the column itself, the blank values come with the matrix because there are no values that match the column, row combination.

Do  you have any other ideas?

[MFelix]

Hi @Anonymous,

In my experience with SWITCH i have used it for constant values but also for variables and ranges depends on the syntax and how you set it up, but maybe in some models and setups it doesn't work.

Can you also share some sample data or a PBIX file?

Regards,

MFelix

[Anonymous]

@MFelix

I got the SWITCH to work.

Days in Status (Base Work) =
VAR Status_Average =
    CALCULATE (
        AVERAGE ( 'NOT-WORKED'[Base Range] ),
        ALL ( Vlookup[Status] )
    )
VAR Rehab_Average =
    CALCULATE (
        AVERAGE ( 'NOT-WORKED'[Base Range] ),
        ALL ( Vlookup[Rehab Status] )
    )
RETURN
    SWITCH (
        TRUE (),
        'NOT-WORKED'[- Status Date] <= 0 + Status_Average
            && 'NOT-WORKED'[- Status Date] >= -10 + Rehab_Average, "Upcoming",
        'NOT-WORKED'[- Status Date] > 0 + Status_Average
            && 'NOT-WORKED'[- Status Date] <= 15 + Rehab_Average, "0 to 15 days",
        'NOT-WORKED'[- Status Date] > 15 + Status_Average
            && 'NOT-WORKED'[- Status Date] <= 30 + Rehab_Average, "16 to 30 days",
        'NOT-WORKED'[- Status Date] > 30 + Status_Average
            && 'NOT-WORKED'[- Status Date] <= 45 + Rehab_Average, "31 to 45 days",
        'NOT-WORKED'[- Status Date] > 45 + Status_Average, "46+ days",
        "x"
    )

When I do the column you suggested I get a circular dependency error

[MFelix]

Hi @Anonymous,

As @Anonymous mentioned this is getting this value because of using a column try to implement this on a measure and should work,

Regards,

MFelix

[Anonymous]

@Anonymous,

Please write measures instead of calculated columns. You can check the difference between measures and calculated columns in the blog below.

http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

Regards,
Lydia

[Anonymous]

@Anonymous @MFelix

Am I supposed to use measures for switch statements? If so, what aggregate should I be using?

[MFelix]

Hi @Anonymous,

You can use the Switch function also in calculated columns however as a best practice you should use measure as long as it possible.

Regards,

MFelix