Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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?
Solved! Go to 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.
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |