Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
adelmonte
Resolver I
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:

Comparison.png

 

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.

Thanks in advance.
Alex

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @adelmonte ,

 

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.

1.png

Create a measure.

Compare pair of columns = 
VAR _ADD =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "Left", LEFT ( [Column Header], 2 ),
        "Right", RIGHT ( [Column Header], 2 )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _ADD,
        "01",
            IF (
                [Right] = "02",
                MAXX (
                    FILTER (
                        _ADD,
                        [Index] = EARLIER ( [Index] )
                            && [left] = EARLIER ( [Left] )
                            && [Right] = "01"
                    ),
                    [Value]
                ),
                BLANK ()
            )
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD1,
        "TRUE/FALSE",
            IF ( [Right] = "02", IF ( [Value] = [01], "True", "False" ), BLANK () )
    )
RETURN
    MAXX (
        FILTER (
            _ADD2,
            [Index] = SUM ( 'Table'[Index] )
                && [Column Header] = MAX ( 'Table'[Column Header] )
        ),
        [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.

1.png

 

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.

View solution in original post

4 REPLIES 4
v-rzhou-msft
Community Support
Community Support

Hi @adelmonte ,

 

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.

1.png

Create a measure.

Compare pair of columns = 
VAR _ADD =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "Left", LEFT ( [Column Header], 2 ),
        "Right", RIGHT ( [Column Header], 2 )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _ADD,
        "01",
            IF (
                [Right] = "02",
                MAXX (
                    FILTER (
                        _ADD,
                        [Index] = EARLIER ( [Index] )
                            && [left] = EARLIER ( [Left] )
                            && [Right] = "01"
                    ),
                    [Value]
                ),
                BLANK ()
            )
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD1,
        "TRUE/FALSE",
            IF ( [Right] = "02", IF ( [Value] = [01], "True", "False" ), BLANK () )
    )
RETURN
    MAXX (
        FILTER (
            _ADD2,
            [Index] = SUM ( 'Table'[Index] )
                && [Column Header] = MAX ( 'Table'[Column Header] )
        ),
        [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.

1.png

 

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.

Thank you very much RicoZhou

amitchandak
Super User
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",
// Add more conditions
"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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.