topic IF statement with multiple columns and logical tests in DAX Commands and Tips

IF statement with multiple columns and logical tests

einrikr — Thu, 27 Feb 2020 13:37:28 GMT

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?

Re: IF statement with multiple columns and logical tests

Anonymous — Thu, 27 Feb 2020 14:06:23 GMT

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

Thanks.

Best
D

Re: IF statement with multiple columns and logical tests

Greg_Deckler — Thu, 27 Feb 2020 14:08:02 GMT

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.

Re: IF statement with multiple columns and logical tests

einrikr — Fri, 28 Feb 2020 08:23:47 GMT

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!