Forum Discussion

WaheedAli's avatar
WaheedAli
Icon for Helper I rankHelper I
5 years ago
Solved

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

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 colu...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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's avatar
WaheedAli
Icon for Helper I rankHelper I
5 years ago

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's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

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's avatar
    WaheedAli
    Icon for Helper I rankHelper I
    5 years ago

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