Multiple columns comparison with a measure or calculated column.

[adelmonte]

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.

Thanks in advance.
Alex

[v-rzhou-msft · Accepted solution]

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.

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.

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.

[adelmonte]

Thank you very much RicoZhou

[amitchandak]

@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

[adelmonte]

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