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,
Fairly new to Power BI and I can't seem to find what I am doing wrong via Google or this forum. I am looking to create a calculated column on a table (Dataset) that selects a row value based on IF or SWITCH statement logic. I've tried both statement types but get the same result.
The IF statement I've been working on:
Compare Column =
IF(
ISBLANK('Dataset'[Column1])==FALSE,
'Dataset'[Column1],
IF(ISBLANK('Dataset'[Column1])==TRUE,
'Dataset'[Column2],
IF(ISBLANK('Dataset'[Column1])==TRUE && ISBLANK('Dataset'[Column2])==TRUE,
'Dataset'[Column3],
IF(ISBLANK('Dataset'[Column1])==TRUE && ISBLANK('Dataset'[Column2])==TRUE && ISBLANK('Dataset'[Column3])==TRUE,
'Dataset'[Column4],
""))))
The SWITCH statement I've been working on:
Compare Column =
SWITCH(
TRUE(),
NOT(ISBLANK('Dataset'[Column1])),
'Dataset'[Column1],
ISBLANK('Dataset'[Column1]),
'Dataset'[Column2],
ISBLANK('Dataset'[Column1]) && ISBLANK('Dataset'[Column2]),
'Dataset'[Column3],
ISBLANK('Dataset'[Column1]) && ISBLANK('Dataset'[Column2]) && ISBLANK('Dataset'[Column3]),
'Dataset'[Column4],
"")
Once Column1 is blank, the calculated column (Compare Column) row value returns as blank.
For example, this is how my current calculated column returns:
Key | Column1 | Column2 | Column3 | Column4 | Compare Column |
1234 | A | B | C | D | A |
1235 | B | C | D | ||
1236 | C | D | |||
1237 | D | ||||
1238 |
What I am wanting to achieve:
Key | Column1 | Column2 | Column3 | Column4 | Compare Column |
1234 | A | B | C | D | A |
1235 | B | C | D | B | |
1236 | C | D | C | ||
1237 | D | D | |||
1238 |
What am I doing wrong here...?
Thanks,
Lejaregg
Try reversing the Switch Values;
Compare Column =
SWITCH(
TRUE(),
ISBLANK('Dataset'[Column1]) && ISBLANK('Dataset'[Column2]) && ISBLANK('Dataset'[Column3]),
'Dataset'[Column4],
ISBLANK('Dataset'[Column1]) && ISBLANK('Dataset'[Column2]),
'Dataset'[Column3],
ISBLANK('Dataset'[Column1]),
'Dataset'[Column2],
NOT(ISBLANK('Dataset'[Column1])),
'Dataset'[Column1],
"")
.
Also, please share some sample data for us to check further.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |