IF statement with multiple columns and logical tests

[einrikr]

Hi all,

I’m trying to make a column with values from several other columns:

If Validation Result OET = “obligatory test” and Validation Date OET = blank, new column should say: “obligatory test”.

In the same column, I would like to have:

If Days to DET Validation Deadline < 0, and Validation Date DET = blank, “Not Timely”

If Days to OET Validation Deadline < 0, and Validation Date OET = blank, “Not Timely”

If Days to DET Validation Deadline < 0, and =< 30, and Validation Date DET = blank, “0-30”

If Days to OET Validation Deadline < 0, and =< 30, and Validation Date OET = blank, “0-30”

If Days to DET Validation Deadline < 30, and =< 60, and Validation Date DET = blank, “30-60”

If Days to OET Validation Deadline < 30, and =< 60, and Validation Date OET = blank, “30-60”

Else, “over 60”

All in all, the new column should be giving me either:

• Obligatory test
• Not Timely
• 0-30
• 30-60
• Over 60

Is it possible to put this in 1 string, and therefore in one column, or is it better to approach this in a completely different way?

There are several other statuses in the “Validation Result DET” and “Validation Result OET” columns.

It is possible to have a finding which has “Effective” as a DET result and “Obligatory test” as OET result, so I figured I can’t just filter out all DET Effectives for example. Therefore I think I need to put it in one IF statement…

Does anybody know what’s best in this matter?

[Greg_Deckler · Accepted solution]

So something like the following?

Column = 
  SWITCH(
    TRUE(),
    [Validation Result OET] = “obligatory test” && ISBLANK([Validation Date OET]), "obligatory test",
    [Days to DET Validation Deadline] < 0 && ISBLANK([Validation Date DET]), “Not Timely”,
    [Days to OET Validation Deadline] < 0 && ISBLANK([Validation Date OET]), “Not Timely”,
    [Days to DET Validation Deadline] <= 30 && ISBLANK([Validation Date DET]), “0-30”,
    [Days to OET Validation Deadline] <= 30 && ISBLANK([Validation Date OET]), “0-30”,
    [Days to DET Validation Deadline] > 30 && [Days to DET Validation Deadline] <= 60 && ISBLANK(Validation Date DET), "30-60",
    [Days to DET Validation Deadline] > 30 && [Days to DET Validation Deadline] <= 60 && ISBLANK([Validation Date OET]), “30-60”,
    “over 60”
  )

 Warning, probably a few syntax errors hiding in there but this should provide the concept. 

[einrikr]

Thanks for your help!

This does exactly what I want it to do. 

The solution did indeed give some syntax errors but they are easily fixed. 

Thanks a lot!

[Anonymous]

Even though it can be done in DAX, this is a job for Power Query. Please use PQ.

Thanks.

Best
D