cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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 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?

 

Color on column based on the value of different columns.JPG

1 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




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
Super User IV
Super User IV

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 >= 13,
        overlap = 04
    )

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

Liquids error.JPGLiquids table.JPG

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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?It should return 1.JPG

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




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors