topic Data Validation with if statement in DAX Commands and Tips

Data Validation with if statement

Anonymous — Fri, 01 Oct 2021 14:52:33 GMT

Hi all,

In my table there column with Regions and column with countries.

I'm looking for a way to validate if countries match to the region as often we are getting data where for example region is India but country Vietnam.

added a custom column with if statement and it works for one region but when I'm trying to add more I'm getting right output (Great/Not so great) for first region but error for the rest.

I wrote something like that

if[EMPLOYING_REGION_COMBINED] = "India" and [EMPLOYING_COUNTRY] = "India" then "Great"
else if [EMPLOYING_REGION_COMBINED] = "Americas" and [EMPLOYING_COUNTRY] = "Argentina" or "Brazil" or "Canada" or "Chile" or "Colombia" or "Mexico" or "United States" then "Great"
else if [EMPLOYING_REGION_COMBINED] = "Poland" and [EMPLOYING_COUNTRY] = "Poland" then "Great"
else if [EMPLOYING_REGION_COMBINED] = "Vietnam" and [EMPLOYING_COUNTRY] = "Vietnam" then "Great"

else "Not so Great"

my table looks like

Region Country Expected Output what I'm getting

India India Great Great

India Vietnam Not so great Error

Americas Chile Great Error

Americas Mexico Great Error

Poland Poland Great Error

Thank you all in advance

Marcin

Re: Data Validation with if statement

PaulOlding — Fri, 01 Oct 2021 15:31:24 GMT

Hi Anonymous

That looks like M code from Power Query, correct?

The issue is the list of or values. You need to respecify the column name each time.

So...

if [EMPLOYING_REGION_COMBINED] = "India" and [EMPLOYING_COUNTRY] = "India" then "Great" else if [EMPLOYING_REGION_COMBINED] = "Americas" and ([EMPLOYING_COUNTRY] = "Argentina" or [EMPLOYING_COUNTRY] = "Brazil" or [EMPLOYING_COUNTRY] = "Canada" or [EMPLOYING_COUNTRY] = "Chile" or [EMPLOYING_COUNTRY] = "Colombia" or [EMPLOYING_COUNTRY] = "Mexico" or [EMPLOYING_COUNTRY] = "United States") then "Great" else if [EMPLOYING_REGION_COMBINED] = "Poland" and [EMPLOYING_COUNTRY] = "Poland" then "Great" else if [EMPLOYING_REGION_COMBINED] = "Vietnam" and [EMPLOYING_COUNTRY] = "Vietnam" then "Great" else "Not so Great"

Re: Data Validation with if statement

Anonymous — Mon, 04 Oct 2021 10:26:03 GMT

Hi Paul,

Thank you very much.