Nested If Statement

[Anonymous]

Hi,

I'm trying to achieve the following outcome in Bi using nested If Statements.

If a items status is invalid I want to do a datediff formula (This part works) 

If the status is invalid and the cert date is blank instead of a date diff be 0

If the status is not Invalid instead of the date diff be 0

This is the Dax Statement I've written. 

IF(JEB_GAS[pdf gas cert status (groups)]="Invalid",DATEDIFF(JEB_GAS[pdf cert date],JEB_GAS[index run date].[Date],dAY), IF(JEB_GAS[pdf gas cert status (groups)]="Invalid" && JEB_GAS[pdf cert date].[Date] = "","0","0"))

[v-shex-msft · Accepted solution]

HI @Anonymous ,

I'd like to suggest you use if statement version, it looks simpler than switch function:

Formula =
IF (
    JEB_GAS[pdf gas cert status (groups)] = "Invalid",
    IF (
        JEB_GAS[pdf cert date].[Date] = "",
        "0",
        DATEDIFF ( JEB_GAS[pdf cert date], JEB_GAS[index run date].[Date], DAY )
    ),
    "0"
)

Regards,

Xiaoxin Sheng

[sturlaws]

Hi @Anonymous,

you are not saying anything about what's not working.

I have rewritten your code slightly, but not sure if it solves anything:

a =
SWITCH (
    TRUE (),
    JEB_GAS[pdf gas cert status (groups)] = "Invalid", DATEDIFF ( JEB_GAS[pdf cert date], JEB_GAS[index run date], DAY ),
    JEB_GAS[pdf gas cert status (groups)] = "Invalid"
        && ISBLANK ( JEB_GAS[pdf cert date] ), 0,
    JEB_GAS[pdf gas cert status (groups)] <> "Invalid", 0,
    0 --else part of the statement
)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

Hi @sturlaws,

Thanks for this, I hope this finds you well its working but i'm getting some blanks.

The cert status groups originate from these three statuses 

Missing Data = Invalid

Expired = Invalid

Valid = Valid

For some odd reason the blanks are only relating to the Missing data records.

Would it be easier to amend the DAX to look at each individual status and not the groups to make sure there are no blanks?

[sturlaws]

sorry, I need more information in order to help you. Can you share a screen shot of where you get your blanks? Or even better share a copy of your report?(only if it doesn't contain sensitive data. If it does, consider making a relevant samle report)

Cheers,
Sturla

[Anonymous]

Hi @sturlaws 

I've used your formula which has helped me massively, Please see below.

When I go into the data view when I select the drop down to see all the results in the new column I can select blanks. 

I would like those blanks to be zeros but i'm not quite sure why they are showing up as they are part of the Invalid group

[sturlaws]

my bad, change the order of the statements to this:

a =
SWITCH (
    TRUE (),
    JEB_GAS[pdf gas cert status (groups)] = "Invalid"
        && ISBLANK ( JEB_GAS[pdf cert date] ), 0,
    JEB_GAS[pdf gas cert status (groups)] = "Invalid", DATEDIFF ( JEB_GAS[pdf cert date], JEB_GAS[index run date], DAY ),
    JEB_GAS[pdf gas cert status (groups)] <> "Invalid", 0,
    0
)

In the Switch-statement, the statements are evaluated from top to bottom, and ends when a statement evaluates to true. Previously, when it the first statement evaluated to true when [pdf gas cert status(groups)]="Invalid", it calculated and returned datediff even if JEB_GAS[pdf cert date] is blank. Now the order is reversed, so it should work. However, you will still get blanks if JEB_GAS[index run date] is blank.

-s