Switch statement based on the two columns with multiple conditions in Power Bi-CodePudding

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

CodePudding user response：

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 )