Creating a conditional column with null value

[sammyboi]

Hi there!

I am trying to create a conditional column to return values based on the following logic:

if column a is null, then column a should equal to column b

if column a and column b are both null, then column a should equal column c

all else should equal to column a

Could you please advise how I should phrase this using the Conditonal Column function?

[MahyarTF]

Hi,

In addition to the @grantsamborn Post, you could create the below column and use it in your visual :

ResultCol =

            SWITCH(Sheet186[Column],

                   BLANK(), SWITCH(Sheet186[A],

                                   BLANK(), SWITCH(Sheet186[B],

                                                   BLANK(), Sheet186[C], Sheet186[B]),

                                   Sheet186[A]),

                  Sheet186[Column])

Appreciate your Kudos

[grantsamborn]

A couple questions/comments:

1 - When creating a calculated column, the table name isn't necessary.
2 - I'm not sure where you came up with Sheet186[Column].
3 - While I realize SWITCH has its places, please explain how in a simple case like this it easier to read than using IF.

grantsamborn =
IF(
    ISBLANK( [ColumnA] ),
    IF(
        ISBLANK( [ColumnB] ),
        [ColumnC],
        [ColumnB]
    ),
    [ColumnA]
)


MahyarTF =
SWITCH(
    Sheet186[Column],
    BLANK(),
        SWITCH(
            Sheet186[A],
            BLANK(),
                SWITCH(
                    Sheet186[B],
                    BLANK(), Sheet186[C],
                    Sheet186[B]
                ),
            Sheet186[A]
        ),
    Sheet186[Column]
)

[MahyarTF]

Hi,

1- Yes, You are right, for the calc column not need to bring the table name (as in the sample file, I have a multi same columns, I wrote the table name and just copy the code)

2- my Table structure is like below (I bring one more column ) :

3- As you know there is more than one solution. I mentioned that your answer I good, but sometimes the codes' performance should be considered.

As you see in below image I run the codes for comparing the performance and it should be not same (should check in real data by user to find the best solution :

Thanks for letting me know

[grantsamborn]

Correct me if I am wrong, but I believe calculated columns are only calculated on refresh of the dataset - NOT on the refresh of visuals.

[danextian]

That is correct. Or also upon editing the calculated column.

[grantsamborn]

This is the first I've heard of a sample file.

Good luck.

[grantsamborn]

Based on your description:
if column a is null, then column a should equal to column b
if column a and column b are both null, then column a should equal column c
all else should equal to column a

I'm guessing that you mean something like this. If not, I don't understand.
if column a is null, then RESULT should equal column b
if column a and column b are both null, then RESULT should equal column c
else RESULT should equal to column a

If I do understand, is this what you are looking for?

z=
IF(
	ISBLANK( [ColumnA] ),
	IF(
		ISBLANK( [ColumnB] ),
		[ColumnC],
		[ColumnB]
	),
	[ColumnA]
)

(If the difference is between NULLs and BLANKs, maybe PowerQuery would be the place to make the substitution.)