Comparing values in a row

[Anonymous]

Dear Community, 

I have a matrix with 1 row and 3 columns, and one value,
I wanted to compare values of each row , if all values are same, change the color of row to blue, if they are not same change it to gray, 
how can I handle this with DAX ? 

[Icey]

Hi @Anonymous ,

Try this:

Measure = 
VAR t1 =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "Sum",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Rows] = MAX ( 'Table'[Rows] ) )
            )
    )
VAR Disc_C1 =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Columns1] ), ALLSELECTED ( 'Table' ) )
VAR Disc_C2 =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Columns2] ), ALLSELECTED ( 'Table' ) )
VAR Disc_C3 =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Columns3] ), ALLSELECTED ( 'Table' ) )
VAR Disc_ =
    SWITCH (
        TRUE (),
        ISFILTERED ( 'Table'[Columns3] ) && ISFILTERED ( 'Table'[Columns2] )
            && ISFILTERED ( 'Table'[Columns1] ),
            Disc_C3 * Disc_C2 * Disc_C1,
        ISFILTERED ( 'Table'[Columns2] ) && ISFILTERED ( 'Table'[Columns1] ), Disc_C2 * Disc_C1,
        ISFILTERED ( 'Table'[Columns1] ), Disc_C1,
        ISFILTERED ( 'Table'[Columns3] )
            && NOT ( ISFILTERED ( 'Table'[Columns2] ) )
                && NOT ( ISFILTERED ( 'Table'[Columns1] ) ), Disc_C3,
        ISFILTERED ( 'Table'[Columns2] )
            && NOT ( ISFILTERED ( 'Table'[Columns3] ) )
                && NOT ( ISFILTERED ( 'Table'[Columns1] ) ), Disc_C2,
        ISFILTERED ( 'Table'[Columns1] )
            && NOT ( ISFILTERED ( 'Table'[Columns2] ) )
                && NOT ( ISFILTERED ( 'Table'[Columns3] ) ), Disc_C1
    )
VAR t2 =
    ADDCOLUMNS ( t1, "Avg", [Sum] / Disc_ )
VAR Avg_ =
    AVERAGEX ( t2, [Avg] )
RETURN
    IF ( SUM ( 'Table'[Value] ) = Avg_, 0, 1 )

Measure 2 = 
SWITCH (
    TRUE (),
    ISFILTERED ( 'Table'[Columns3] ) && ISFILTERED ( 'Table'[Columns2] )
        && ISFILTERED ( 'Table'[Columns1] ),
        MAXX (
            ALLSELECTED ( 'Table'[Columns1], 'Table'[Columns2], 'Table'[Columns3] ),
            [Measure]
        ),
    ISFILTERED ( 'Table'[Columns2] ) && ISFILTERED ( 'Table'[Columns1] ), MAXX ( ALLSELECTED ( 'Table'[Columns1], 'Table'[Columns2] ), [Measure] ),
    ISFILTERED ( 'Table'[Columns1] ), MAXX ( ALLSELECTED ( 'Table'[Columns1] ), [Measure] ),
    ISFILTERED ( 'Table'[Columns3] )
        && NOT ( ISFILTERED ( 'Table'[Columns2] ) )
            && NOT ( ISFILTERED ( 'Table'[Columns1] ) ), MAXX ( ALLSELECTED ( 'Table'[Columns3] ), [Measure] ),
    ISFILTERED ( 'Table'[Columns2] )
        && NOT ( ISFILTERED ( 'Table'[Columns3] ) )
            && NOT ( ISFILTERED ( 'Table'[Columns1] ) ), MAXX ( ALLSELECTED ( 'Table'[Columns2] ), [Measure] ),
    ISFILTERED ( 'Table'[Columns1] )
        && NOT ( ISFILTERED ( 'Table'[Columns2] ) )
            && NOT ( ISFILTERED ( 'Table'[Columns3] ) ), MAXX ( ALLSELECTED ( 'Table'[Columns1] ), [Measure] )
)

Best Regards,

Icey

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

[Anonymous]

@Icey Thanks for your help. I got this error, Is it normal ? do you have any idea? 

[Icey]

Hi @Anonymous ,

Just as the error mentioned, thereis not enough memory to complete this operation. Please try to: 

1. stop other irrelevant applications and services

2. Modify the measures like so:

Measure_1 = 
VAR t1 =
    SUMMARIZE (
        'Table',
        'Table'[Rows],
        'Table'[Columns1],
        'Table'[Columns2],
        'Table'[Columns3],
        'Table'[Value],
        "Sum",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Rows] = MAX ( 'Table'[Rows] ) )
            )
    )
VAR Disc_C1 =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Columns1] ), ALLSELECTED ( 'Table' ) )
VAR Disc_C2 =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Columns2] ), ALLSELECTED ( 'Table' ) )
VAR Disc_C3 =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Columns3] ), ALLSELECTED ( 'Table' ) )
VAR Disc_ =
    SWITCH (
        TRUE (),
        ISFILTERED ( 'Table'[Columns3] ) && ISFILTERED ( 'Table'[Columns2] )
            && ISFILTERED ( 'Table'[Columns1] ),
            Disc_C3 * Disc_C2 * Disc_C1,
        ISFILTERED ( 'Table'[Columns2] ) && ISFILTERED ( 'Table'[Columns1] ), Disc_C2 * Disc_C1,
        ISFILTERED ( 'Table'[Columns1] ), Disc_C1,
        ISFILTERED ( 'Table'[Columns3] )
            && NOT ( ISFILTERED ( 'Table'[Columns2] ) )
                && NOT ( ISFILTERED ( 'Table'[Columns1] ) ), Disc_C3,
        ISFILTERED ( 'Table'[Columns2] )
            && NOT ( ISFILTERED ( 'Table'[Columns3] ) )
                && NOT ( ISFILTERED ( 'Table'[Columns1] ) ), Disc_C2
    )
VAR t2 =
    ADDCOLUMNS ( t1, "Avg", [Sum] / Disc_ )
VAR Avg_ =
    AVERAGEX ( t2, [Avg] )
RETURN
    IF ( SUM ( 'Table'[Value] ) = Avg_, 0, 1 )

Measure_2 = 
SWITCH (
    TRUE (),
    ISFILTERED ( 'Table'[Columns3] ) && ISFILTERED ( 'Table'[Columns2] )
        && ISFILTERED ( 'Table'[Columns1] ),
        MAXX (
            ALLSELECTED ( 'Table'[Columns1], 'Table'[Columns2], 'Table'[Columns3] ),
            [Measure_1]
        ),
    ISFILTERED ( 'Table'[Columns2] ) && ISFILTERED ( 'Table'[Columns1] ), MAXX ( ALLSELECTED ( 'Table'[Columns1], 'Table'[Columns2] ), [Measure_1] ),
    ISFILTERED ( 'Table'[Columns1] ), MAXX ( ALLSELECTED ( 'Table'[Columns1] ), [Measure_1] ),
    ISFILTERED ( 'Table'[Columns3] )
        && NOT ( ISFILTERED ( 'Table'[Columns2] ) )
            && NOT ( ISFILTERED ( 'Table'[Columns1] ) ), MAXX ( ALLSELECTED ( 'Table'[Columns3] ), [Measure_1] ),
    ISFILTERED ( 'Table'[Columns2] )
        && NOT ( ISFILTERED ( 'Table'[Columns3] ) )
            && NOT ( ISFILTERED ( 'Table'[Columns1] ) ), MAXX ( ALLSELECTED ( 'Table'[Columns2] ), [Measure_1] )
)

3. try it again. 

Best Regards,

Icey

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

[amitchandak]

@Anonymous , there is nothing like a row-level operation in power bi. So you need to do it using a formula and color measure

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

For color measure

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values