cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I

## Multiple columns comparison with a measure or calculated column.

Hi all,

I need to compare multiple pair of columns.

Desired Outcome in Yelow:

I tried to create 1 Conditional Column in power query with multiple IFs, but it doesn't work.
Instead of creating multiple comparison columns, is there a way to have only one measure or calculated column that checks all conditions and returns a correct value.

Alex

1 ACCEPTED SOLUTION
Community Support

Currently you want to compare multiple pairs of columns in one table. Your logic is if Colum1[Value] = Colum2[Value] then True else False. However Power Query, Dax only support you to compare determind columns instead of dynamic columns. So you need to determind column1 and column2 in your code. Only one measure couldn't compair all pairs of columns.

Here I suggest you to add an Index column in your table then use unpivot function to transform your table.

After transformation, my Sample looks like as below.

Create a measure.

``````Compare pair of columns =
ALL ( 'Table' ),
"Left", LEFT ( [Column Header], 2 ),
"Right", RIGHT ( [Column Header], 2 )
)
"01",
IF (
[Right] = "02",
MAXX (
FILTER (
[Index] = EARLIER ( [Index] )
&& [left] = EARLIER ( [Left] )
&& [Right] = "01"
),
[Value]
),
BLANK ()
)
)
"TRUE/FALSE",
IF ( [Right] = "02", IF ( [Value] = [01], "True", "False" ), BLANK () )
)
RETURN
MAXX (
FILTER (
[Index] = SUM ( 'Table'[Index] )
),
[TRUE/FALSE]
)``````

Create a matrix visual. We only need measure result next column02, so turn off word wrap in Format and reduce the width of measure result for column01 to 0. Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Community Support

Currently you want to compare multiple pairs of columns in one table. Your logic is if Colum1[Value] = Colum2[Value] then True else False. However Power Query, Dax only support you to compare determind columns instead of dynamic columns. So you need to determind column1 and column2 in your code. Only one measure couldn't compair all pairs of columns.

Here I suggest you to add an Index column in your table then use unpivot function to transform your table.

After transformation, my Sample looks like as below.

Create a measure.

``````Compare pair of columns =
ALL ( 'Table' ),
"Left", LEFT ( [Column Header], 2 ),
"Right", RIGHT ( [Column Header], 2 )
)
"01",
IF (
[Right] = "02",
MAXX (
FILTER (
[Index] = EARLIER ( [Index] )
&& [left] = EARLIER ( [Left] )
&& [Right] = "01"
),
[Value]
),
BLANK ()
)
)
"TRUE/FALSE",
IF ( [Right] = "02", IF ( [Value] = [01], "True", "False" ), BLANK () )
)
RETURN
MAXX (
FILTER (
[Index] = SUM ( 'Table'[Index] )
),
[TRUE/FALSE]
)``````

Create a matrix visual. We only need measure result next column02, so turn off word wrap in Format and reduce the width of measure result for column01 to 0. Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Resolver I

Thank you very much RicoZhou

Super User

@adelmonte , You can create a color measure and use that in conditional formatting. It can be as complex as you want, as long as it works

example

color =
switch ( true(),
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity1" && sum('Table'[Value]) >500,"lightgreen",
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity2" && sum('Table'[Value]) >1000,"lightgreen",
"red"
)

Arrow color =
var _change =[Net Sales YTD]-[Net Sales LYTD]
return
SWITCH (
TRUE(),
_change > 0, "green",
_change = 0, "blue",
_change < 0, "red"
)

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

Resolver I

Amit please read well post and don't spam post if you don't have a solution

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors