0
192
ViewsSwitch statement based on the two columns with multiple conditions in Power Bi
I have a two columns are A and B, Both columns are contains text and sometimes text and numbers.
I would like to generate new calculate column by using DAX based on the column A and B with below mentioned rules.
If column A equal to DNK and B equal to Apple then result is ok
If column A equal to DNK and B not equal to Apple then result is not ok
If column A equal to DNK and B is NA then result is XX.
DNK expecting three different results based on the columns A&B with multiple scenarios and the same thing for ADNK and BJB.
If column A equal to ADNK and B equal to Orange then result is ok
If column A equal to ADNK and B not equal to Orange then result is not ok
If column A equal to ADNK and B is NA then result is XX.
If column A equal to BJB and B equal to Apple1 then result is ok
If column A equal to BJB and B not equal to Apple1 then result is not ok
If column A equal to BJB and B is NA then result is XX.
A B RESULT
DNK APPLE OK
DNK APPLE OK
DNK ORANGE NOT OK
DNK ORANGE NOT OK
DNK APPLE OK
DNK APPLE OK
DNK NA XX
DNK NA XX
ADNK ORANGE OK
ADNK ORANGE OK
ADNK ORANGE OK
ADNK ORANGE OK
ADNK NA XX
ADNK APPLE NOT OK
ADNK APPLE NOT OK
BJB APPLE1 OK
BJB ORANGE1 NOT OK
BJB APPLE1 OK
BJB ORANGE1 NOT OK
BJB APPLE1 OK
BJB APPLE1 OK
BJB NA XX
BJB NA XX
BJB ORANGE1 NOT OK
BJB APPLE1 OK
1 answers
Answer question0
Based on what you asked, you can achieve it in two ways
a. by optimizing SWITCH like following
desiredColumn1 =
SWITCH (
TRUE (),
[A] <> BLANK ()
&& [B] = "NA", "XX",
[A]="DNK"&&[B]="Apple"||[A]="ADNK"&&[B]="Orange"||[A]="BJB"&&[B]="Apple1","ok",
"not ok"
)
b. by building a whiteList Table and iterating that in the fact
desiredColumn2 =
VAR _whiteList =
DATATABLE (
"a1", STRING,
"b1", STRING,
"c1", STRING,
{
{ "DNK", "Apple", "ok" },
{ "DNK", "NA", "XX" },
{ "ADNK", "Orange", "ok" },
{ "ADNK", "NA", "XX" },
{ "BJB", "Apple1", "ok" },
{ "BJB", "NA", "XX" }
}
)
VAR _fromList =
MAXX (
FILTER (
_whiteList,
[a1] = EARLIER ( 'Table'[A] )
&& [b1] = EARLIER ( 'Table'[B] )
),
[c1]
)
RETURN
IF ( _fromList = BLANK (), "not ok", _fromList )