Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Nested If Statement

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...
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
sturlaws
Resident Rockstar
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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?