Forum Discussion

Lejaregg's avatar
Lejaregg
New Member
6 years ago

Question: Issues with Calculated Column Using IF or SWITCH Logic

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:

KeyColumn1Column2Column3Column4Compare Column
1234ABCDA
1235 BCD 
1236  CD 
1237   D 
1238     

 

What I am wanting to achieve:

KeyColumn1Column2Column3Column4Compare Column
1234ABCDA
1235 BCDB
1236  CDC
1237   DD
1238     

 

What am I doing wrong here...?

 

Thanks,

Lejaregg

Need Help
Tutorial Requests

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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