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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Conditional Formatting Matrix

Hello Power BI Community,

 

I am trying to create a conditional format on a matrix. The conditional format I envision will be applied on a row-by-row level.

 

I am pretty close. I hope you can help me get the football over the goal line. 

 

Here is the link to the data and PBIX. The data is fake but the problem is real.

 

Data and PBIX 

 

My output so far.

 

quipmaster_1-1695943092631.png

My Issue

CF Measure is applied on conditional formatting

Goal of CF measure

 

conditional formatting on a row by row basis

 

Issues with CF measure

  1. Different color for exact values. see Approaches row. I would like 6 and 6 to be the same color.
  2. Did Not Meet should be red if greater than 5. 

Here is my CF Measure

CF =
VAR SummaryTable =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'Test and Intervention Hours',
                Campus[Intervention Teacher Last Name],
                'Test and Intervention Hours'[Performance Level]
            ),
            "Students", [Total Students]
        ),
        ALLSELECTED ( 'Test and Intervention Hours'[Performance Level])
    )
VAR MaxValue =
    MAXX ( SummaryTable, [Total Students]  )
VAR MinValue =
    MINX ( SummaryTable, [Total Students] )
VAR Range = MaxValue - MinValue
VAR Shade =
    ROUND ( DIVIDE (
        [Total Students] - MinValue,
        RANGE )  * 120, 0 )
VAR Color = "hsla(" & Shade & ", " & "100%" & "," & "90%" & "," & 1 & ")"
RETURN
    Color
 
All guidance is greatly appreciated.
 
Luis

 

 

 

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @Anonymous 

  1. You should use ALLSELECTED () with no arguments if you want the Range to be based on all values in the visual. This will ensure that the same number receives the same format.
  2. You need to add a branch to test whether Total Students > 5 & Performance Level = "Did Not Meet", and return the required "red" colour in that case.

I have attached an edited version of the PBIX.

 

There are two CF measures I tried:

  1. CF: The only change is to force Total Students > 5 & Did Not Meet to "red".
  2. CF v2: Same as CF, but the range is based only on the values excluding Total Students > 5 & Did Not Meet.

OwenAuger_0-1695975246055.png

Here are the measures:

CF = 
VAR TotalStudents =
    [Total Students]
VAR PerformanceLevel =
    SELECTEDVALUE ( 'Test and Intervention Hours'[Performance Level] )
VAR DidNotMeetgreaterThan5 =
    AND (
        PerformanceLevel = "Did Not Meet",
        TotalStudents > 5
    )
VAR OverrideColour = "red" -- adjust as needed
VAR Color =
    IF (
        DidNotMeetgreaterThan5,
        -- If Did Not Meet & Total Students > 5 then use OverrideColour
        OverrideColour,
        -- Otherwise use normal rule
        VAR SummaryTable =
            CALCULATETABLE (
                ADDCOLUMNS (
                    SUMMARIZE (
                        'Test and Intervention Hours',
                        Campus[Intervention Teacher Last Name],
                        'Test and Intervention Hours'[Performance Level]
                    ),
                    "Students", [Total Students]
                ),
                ALLSELECTED ()
            )
        VAR MaxValue = MAXX ( SummaryTable, [Students] )
        VAR MinValue = MINX ( SummaryTable, [Students] )
        VAR Range = MaxValue - MinValue
        VAR Shade =
            ROUND (
                DIVIDE ( TotalStudents - MinValue, Range ) * 120,
                0
            )
        RETURN
        "hsla(" & Shade & ", " & "100%" & "," & "90%" & "," & 1
        & ")"
    )
RETURN
    Color
CF v2 = 
VAR TotalStudents =
    [Total Students]
VAR PerformanceLevel =
    SELECTEDVALUE ( 'Test and Intervention Hours'[Performance Level] )
VAR DidNotMeetgreaterThan5 =
    AND (
        PerformanceLevel = "Did Not Meet",
        TotalStudents > 5
    )
VAR OverrideColour = "red" -- adjust as needed
VAR Color =
    IF (
        DidNotMeetgreaterThan5,
        -- If Did Not Meet & Total Students > 5 then use OverrideColour
        OverrideColour,
        -- Otherwise use normal rule
        VAR SummaryTable =
            FILTER (
                CALCULATETABLE (
                    ADDCOLUMNS (
                        SUMMARIZE (
                            'Test and Intervention Hours',
                            Campus[Intervention Teacher Last Name],
                            'Test and Intervention Hours'[Performance Level]
                        ),
                        "Students", [Total Students]
                    ),
                    ALLSELECTED ()
                ),
                NOT AND (
                    'Test and Intervention Hours'[Performance Level] = "Did Not Meet",
                    [Students] > 5
                )
            )
        VAR MaxValue = MAXX ( SummaryTable, [Students] )
        VAR MinValue = MINX ( SummaryTable, [Students] )
        VAR Range = MaxValue - MinValue
        VAR Shade =
            ROUND (
                DIVIDE ( TotalStudents - MinValue, Range ) * 120,
                0
            )
        RETURN
        "hsla(" & Shade & ", " & "100%" & "," & "90%" & "," & 1
        & ")"
    )
RETURN
    Color

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

Hi @Anonymous 

  1. You should use ALLSELECTED () with no arguments if you want the Range to be based on all values in the visual. This will ensure that the same number receives the same format.
  2. You need to add a branch to test whether Total Students > 5 & Performance Level = "Did Not Meet", and return the required "red" colour in that case.

I have attached an edited version of the PBIX.

 

There are two CF measures I tried:

  1. CF: The only change is to force Total Students > 5 & Did Not Meet to "red".
  2. CF v2: Same as CF, but the range is based only on the values excluding Total Students > 5 & Did Not Meet.

OwenAuger_0-1695975246055.png

Here are the measures:

CF = 
VAR TotalStudents =
    [Total Students]
VAR PerformanceLevel =
    SELECTEDVALUE ( 'Test and Intervention Hours'[Performance Level] )
VAR DidNotMeetgreaterThan5 =
    AND (
        PerformanceLevel = "Did Not Meet",
        TotalStudents > 5
    )
VAR OverrideColour = "red" -- adjust as needed
VAR Color =
    IF (
        DidNotMeetgreaterThan5,
        -- If Did Not Meet & Total Students > 5 then use OverrideColour
        OverrideColour,
        -- Otherwise use normal rule
        VAR SummaryTable =
            CALCULATETABLE (
                ADDCOLUMNS (
                    SUMMARIZE (
                        'Test and Intervention Hours',
                        Campus[Intervention Teacher Last Name],
                        'Test and Intervention Hours'[Performance Level]
                    ),
                    "Students", [Total Students]
                ),
                ALLSELECTED ()
            )
        VAR MaxValue = MAXX ( SummaryTable, [Students] )
        VAR MinValue = MINX ( SummaryTable, [Students] )
        VAR Range = MaxValue - MinValue
        VAR Shade =
            ROUND (
                DIVIDE ( TotalStudents - MinValue, Range ) * 120,
                0
            )
        RETURN
        "hsla(" & Shade & ", " & "100%" & "," & "90%" & "," & 1
        & ")"
    )
RETURN
    Color
CF v2 = 
VAR TotalStudents =
    [Total Students]
VAR PerformanceLevel =
    SELECTEDVALUE ( 'Test and Intervention Hours'[Performance Level] )
VAR DidNotMeetgreaterThan5 =
    AND (
        PerformanceLevel = "Did Not Meet",
        TotalStudents > 5
    )
VAR OverrideColour = "red" -- adjust as needed
VAR Color =
    IF (
        DidNotMeetgreaterThan5,
        -- If Did Not Meet & Total Students > 5 then use OverrideColour
        OverrideColour,
        -- Otherwise use normal rule
        VAR SummaryTable =
            FILTER (
                CALCULATETABLE (
                    ADDCOLUMNS (
                        SUMMARIZE (
                            'Test and Intervention Hours',
                            Campus[Intervention Teacher Last Name],
                            'Test and Intervention Hours'[Performance Level]
                        ),
                        "Students", [Total Students]
                    ),
                    ALLSELECTED ()
                ),
                NOT AND (
                    'Test and Intervention Hours'[Performance Level] = "Did Not Meet",
                    [Students] > 5
                )
            )
        VAR MaxValue = MAXX ( SummaryTable, [Students] )
        VAR MinValue = MINX ( SummaryTable, [Students] )
        VAR Range = MaxValue - MinValue
        VAR Shade =
            ROUND (
                DIVIDE ( TotalStudents - MinValue, Range ) * 120,
                0
            )
        RETURN
        "hsla(" & Shade & ", " & "100%" & "," & "90%" & "," & 1
        & ")"
    )
RETURN
    Color

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

That was perfect @OwenAuger !

 

I can follow your logic.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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