topic Row by row Conditional Formatting on a Matrix in DAX Commands and Tips

Row by row Conditional Formatting on a Matrix

Anonymous — Mon, 20 Mar 2023 14:25:42 GMT

Dear all,

I have a matrix formating issue.

I want to conditionally format my matirx row by row to show the monthly highest and smallest values for the years and months selected.

The result should look like this:

I can get the correct formating (below) for the individual dates but not for the Month, by using this DAX expression.

_data rank =

IF (

NOT (ISBLANK ([Sum Ouput])),

RANKX( CALCULATETABLE(

ADDCOLUMNS(

SUMMARIZE(

Table,

Table[Factory Name],

Table[Start Date],

Table[Production],

Table[Machine],

Table[Ref],

'Date'[Date]

"Summerised Data", [Sum Output]

ALLSELECTED('Date'[Date])

[Sum Output], , , Dense

)

Please can you help me rank the data so that I can see the ranking what the data is aggregated on a monthly basis rather than the granular daily values?

Let me know if you require more information. Many thanks in advance.

tagging a few people to help. tamerj1, FreemanZ , amitchandak

Re: Row by row Conditional Formatting on a Matrix

lbendlin — Wed, 22 Mar 2023 01:26:55 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Row by row Conditional Formatting on a Matrix

Anonymous — Tue, 16 May 2023 20:35:06 GMT

Hi,

Thank you for your reply and apologies for my delay in responding.

Please find a pbix file here with a sample data set: pbix file

Here is also an Excel file showing the row by row conditional formating that I want.

Ideally each row of data is conditionally formated based on the highest and lowest value in that row. This formatting should be maintained when I drill down to the day granualrity or drill up to month.

Many thanks in advance!

Re: Row by row Conditional Formatting on a Matrix

lbendlin — Tue, 16 May 2023 22:54:44 GMT

For each Machine and Year you want to find the highest and lowest monthly values? Or for each Machine you want to find the highest and lowest values regardless of the year? You would need to use ISINSCOPE() to figure out where in the date hierarchy you are.

Re: Row by row Conditional Formatting on a Matrix

Anonymous — Wed, 17 May 2023 08:12:46 GMT

For each machine and year, I want to format the matrix so that the value in each row is formated according to how big it is from smallest to largests, ignoring other rows of data. as in the image below: For example for machine 2, 2023 46,466 data is the smallest and 2021 385,213 is the biggest.

I also what this for each month in a year - the value in each row should be formated from smallest to largest, ignoring other rows of data. Please see below example, for Machine two we get more detail than in the before image, we see that for 2022, June had the smallest data and March the largest.

If I drill down then the values in each row again should be formated to show the smallest to biggest, ignoring other rows of data. In the below eaxmple, the daily granulariy show that for Machine 2, in March 2022, March 20 had the smallest data whilst March 14 had the largest data.

Thank for for your reply. I will check out ISINSCOPE() function.

Re: Row by row Conditional Formatting on a Matrix

lbendlin — Thu, 18 May 2023 01:25:23 GMT

See attached.

color =
SWITCH(
    TRUE(),
    ISINSCOPE( 'Date Table'[Day Number] ),
        VAR a =
            CALCULATETABLE(
                SUMMARIZE(
                    'Date Table',
                    'Date Table'[Year],
                    'Date Table'[Month],
                    'Date Table'[Day Number],
                    "sd", [sum data]
                ),
                REMOVEFILTERS(
                    'Date Table'[Year],
                    'Date Table'[Month],
                    'Date Table'[Day Number]
                )
            )
        RETURN
            DIVIDE( [sum data] - MINX( a, [sd] ), MAXX( a, [sd] ) - MINX( a, [sd] ) ),
    ISINSCOPE( 'Date Table'[Month] ),
        VAR a =
            CALCULATETABLE(
                SUMMARIZE(
                    'Date Table',
                    'Date Table'[Year],
                    'Date Table'[Month],
                    "sd", [sum data]
                ),
                REMOVEFILTERS( 'Date Table'[Year], 'Date Table'[Month] )
            )
        RETURN
            DIVIDE( [sum data] - MINX( a, [sd] ), MAXX( a, [sd] ) - MINX( a, [sd] ) ),
    ISINSCOPE( 'Date Table'[Year] ),
        VAR a =
            CALCULATETABLE(
                SUMMARIZE( 'Date Table', 'Date Table'[Year], "sd", [sum data] ),
                REMOVEFILTERS( 'Date Table'[Year] )
            )
        RETURN
            DIVIDE( [sum data] - MINX( a, [sd] ), MAXX( a, [sd] ) - MINX( a, [sd] ) )
)

Re: Row by row Conditional Formatting on a Matrix

Anonymous — Mon, 22 May 2023 08:09:50 GMT

Hi Ibendlin,

Thank you so much for the time spent coming up with the above DAX. I appreciate your efort and time. Unfortunately, it doesn't work quite as I expect. In each row the data colouring doesn't flow from smallest to largest.

I created a work around with the below DAX. I essentially created two of the below DAX, one for formating the monthly view and another for formating the daily view and then added buttons for the use to move between the two matrices rather than drilling up or down. The below DAX is the monthly formating.

month Row by row CF =

VAR SummaryTable =

CALCULATETABLE(

ADDCOLUMNS (

Summarize(

'Data Table name',

'Data Table name'[row granularity column],

'Date Table'[Year],

'Date Table'[Month]

" SummaryTable column name", [sum measure of interest]

ALLSELECTED('Date Table') // column to remove filter so restart every row

)

VAR MaxValue =

MAXX (

SummaryTable,

[SummaryTable column name]

)

VAR MinValue =

MINX (

SummaryTable,

[SummaryTable column name]

)

VAR range = MaxValue - MinValue

VAR hue =

Round(

DIVIDE (

[sum measure of interest] - MinValue,

range

)*60, 0

) // + 240 // - to change the colour hue

VAR colour = "hsla(" & hue & ", " & "100%" & ", " & "70%" & ", " & 1 & ")"

Return

colour

This solution is from Bas, How to Power BI video titled Unleash the full Potential of Conditional Formating- row by row colour scale in a Matrix.

Re: Row by row Conditional Formatting on a Matrix

powerbi2srm — Thu, 21 Sep 2023 17:52:32 GMT

Hi lbendlin ! The code doesn't run properly when you select more than one year in a slicer. The reason is "color" measure isn't between 0 and 1. How could it be fixed?

Some days ago I created a similar question here:

https://community.fabric.microsoft.com/t5/Desktop/Conditional-formatting-row-by-row/m-p/3441922#M1141758

Thank you so much for your time!

Re: Row by row Conditional Formatting on a Matrix

lbendlin — Thu, 21 Sep 2023 20:09:29 GMT

I refactored the code. Seems to work better now.

color = SWITCH ( TRUE (), ISINSCOPE ( 'Date Table'[Day Number] ), VAR a = ADDCOLUMNS ( CALCULATETABLE ( SUMMARIZECOLUMNS ( 'Date Table'[Day Number] ), REMOVEFILTERS ( 'Date Table'[Day Number] ) ), "sd", [sum data] ) RETURN DIVIDE ( [sum data] - MINX ( a, [sd] ), MAXX ( a, [sd] ) - MINX ( a, [sd] ) ), ISINSCOPE ( 'Date Table'[Month] ), VAR a = ADDCOLUMNS ( CALCULATETABLE ( SUMMARIZECOLUMNS ( 'Date Table'[Month], 'Date Table'[Month Number] ), REMOVEFILTERS ( 'Date Table'[Month], 'Date Table'[Month Number] ) ), "sd", [sum data] ) RETURN DIVIDE ( [sum data] - MINX ( a, [sd] ), MAXX ( a, [sd] ) - MINX ( a, [sd] ) ), ISINSCOPE ( 'Date Table'[Year] ), VAR a = ADDCOLUMNS ( CALCULATETABLE ( SUMMARIZECOLUMNS ( 'Date Table'[Year] ), REMOVEFILTERS ( 'Date Table'[Year] ) ), "sd", [sum data] ) RETURN DIVIDE ( [sum data] - MINX ( a, [sd] ), MAXX ( a, [sd] ) - MINX ( a, [sd] ) ) )

Re: Row by row Conditional Formatting on a Matrix

powerbi2srm — Fri, 22 Sep 2023 15:46:10 GMT

That's perfect! Thank you so much for your time.

Re: Row by row Conditional Formatting on a Matrix

powerbi2srm — Wed, 18 Oct 2023 05:27:44 GMT

Hi lbendlin! I just realized if I sort year column by other one, the code doesn't work properly.
I want to sort years inversely as you can see in the next image:

How can I solve this? I doesn't understand why doesn't run. Thank you so much.

Re: Row by row Conditional Formatting on a Matrix

lbendlin — Wed, 18 Oct 2023 20:03:57 GMT

You need to include the sort column in the formula, like you do for the month.

Re: Row by row Conditional Formatting on a Matrix

powerbi2srm — Thu, 19 Oct 2023 08:50:49 GMT

thank you so much!!!😁