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 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"))

 

 

 

 

 

 

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

6 Replies

  • 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?

      • sturlaws's avatar
        sturlaws
        Resident Rockstar
        6 years ago

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