Question: Issues with Calculated Column Using IF or SWITCH Logic

[Lejaregg]

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

[harshnathani]

@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!!