Change value based on value in other column

[Baconbomb]

Hi.

I have a column where i want to change the value IF its blank or # and if the value in a different column is NOT "x" or "y" or "z"

To explain it a little better.

So the column i want to change is a list of countrys and from our DB that some times displayes as # or empty.

So i want to change this to a new calculated column only if the data from a different column is not X or Y or Z

I tried doing a DAX formula but gave up after a couple of hours.

Any ideas on how to do it or to point me in the right direction. 

[v-chuncz-msft · Accepted solution]

@Baconbomb,

You may refer to the following DAX that adds a calculated column.

Column =
IF (
    Table1[Column1] IN { "", "#" }
        && NOT ( Table1[Column2] IN { "x", "y", "z" } ),
    TRUE (),
    FALSE ()
)

[Baconbomb]

@v-chuncz-msft

Maybe im missing something or its the way my data is formulated but here is the "translation" of your formula that i made and i get an error

Country =
 IF(
     'All trafik Nat, roam och data'[Land/Country]={"";"#"} 
        && NOT 'All trafik Nat, roam och data'[Call Descr]={"I utländska 
        nät";"GPRS/3G i utlandet";"Till fastnät utomlands";"SMS till utlandet";"Till 
        mobil utomlands"};
    "Sweden";
    'All trafik Nat, roam och data'[Land/Country])

A table of multiple values was supplied where a single value was expected.

[v-chuncz-msft]

@Baconbomb,

Notice the IN operator above.

[Baconbomb]

Missed them. Now it works.

Thanks for the help.

[jthomson]

Sounds like it's a job for a conditional column in Power Query, doing it backwards to how you describe - have your new column equal the country column if the third column equals x, y or z, then what you've got left are just the entries that you would want to change, you can then have it look at the value of the country column and change it as necessary for each instance (#, blank) you need to, having it maintain the value of the country column at the end for those where you don't need to change it.

[MarcelBeug]

A few remarks:

First, you don't mention what the changed value should be.

Second, if a column is added, then this column will be populated with values for all rows.

So you need to define what the value would be if the condition is met and what the value would be if the condition is not met.

Is Power Query OK or do you need DAX?