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?

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

        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