Create a measure to find multiple texts and return a specific value in a variable

[WaheedAli]

I am relatively new with Power BI and coding/programming so please bear with me. What I want is to get three different values (e.g. 1, 2 & 3), as output in a variable based on the values in many columns.

For example in the below table

If a row contains 3 specific liquid (e.g. pespi, water & fanta) types (only), in all columns, it should return 1,

And if a row has same (pepsi, water, fanta) 3 liquids as well as other liquid types, in all columns, it should return 2

And if a row has only 1 of 3 liquids (pepsi, water, fanta) it should return 3

But if a row does not have all these (pepsi, water, fanta) 3 specific liquid types, in all columns, it should return 4.

Remember these there liquids in all the columns can be in any random column

Can someone guide me what DAX formula I have to use or how to create this measure?

[mahoneypat]

Normally I would suggest unpivoting your data.  But this column expression gets your result in the current format.

Code =
VAR liqs =
    DISTINCT ( {
        Liquids[Liquid1],
        Liquids[Liquid2],
        Liquids[Liquid3],
        Liquids[Liquid4],
        Liquids[Liquid5]
    } )
VAR comparetable = {
    "Pepsi",
    "Fanta",
    "Water"
}
VAR overlap =
    COUNTROWS (
        INTERSECT (
            comparetable,
            liqs
        )
    )
VAR liqcount =
    COUNTROWS (
        DISTINCT ( liqs )
    )
RETURN
    SWITCH (
        TRUE (),
        AND (
            overlap = 3,
            liqcount = 3
        ), 1,
        AND (
            overlap = 3,
            liqcount > 3
        ), 2,
        overlap >= 1, 3,
        overlap = 0, 4
    )

Regards,

Pat

[WaheedAli]

Dear Pat,

Thank you very much for helping but the formula is not working. I am attaching the screenshot. But I think when you use DISTINCT operator is does not allow you select table/column and maybe thats how it operates. But since you are the expert, you can best read the error and see what is the problem.

The file can be PIBX file can be downloaded from this LINK. 

And FYI I am trying to use these variable results (1,2,3) to conditional format the names based on those three liquids presence in a row.

[mahoneypat]

The expression I provided is a column expression, not a measure.  In Data View for the liquids table, click on New Column in the ribbon, and enter that expression.  Since the data are not unpivoted and the table of values is being created across each row, a column expression was simpler.  It is possible to do this as a measure,but it would be even longer and I would suggest unpivoting the data and writing a simpler measure at that point.

Regards,

Pat

[WaheedAli]

Dear Pat,

This is really helpful and I really appreciate your time on it. Just one small thing. Its not returning 1, where the liquids are only the desired three types i.e. Pepsi, Fanta & Water. It is returns 2. I tried all my logics and testing but could not make it return two. It might be a small thing for you, can you please have a look at the code again?

[mahoneypat · Accepted solution]

Please use this variation to take out the blank values from the evaluations.

NewColumn = VAR liqs =

Filter(DISTINCT ( {

Liquids[Liquid1],

Liquids[Liquid2],

Liquids[Liquid3],

Liquids[Liquid4],

Liquids[Liquid5]

} ), [Value] <> "")

VAR comparetable = {

"Pepsi",

"Fanta",

"Water"

}

VAR overlap =

COUNTROWS (

INTERSECT (

comparetable,

liqs

)

)

VAR liqcount =

COUNTROWS (

DISTINCT ( liqs )

)

RETURN

SWITCH (

TRUE (),

AND (

overlap = 3,

liqcount = 3

), 1,

AND (

overlap = 3,

liqcount > 3

), 2,

overlap >= 1, 3,

overlap = 0, 4

)

Regards,

Pat

[WaheedAli]

Thank you very much pat. Realy much appreciated. You saved me... Thanks a ton... Its working perfectly 🙂