Forum Discussion
Comparing values in a row
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 ?
4 Replies
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.
Icey Thanks for your help. I got this error, Is it normal ? do you have any idea?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.
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
