Forum Discussion
Coloring Values Assistance
Hello community!
I have created a matrix and a table on the same tab of a Power BI Report. (See 1st picture below)
The table on the left uses conditional columns which I manually inserted into the table. (For example: If the time is 8:00 AM, then 15 staff is then 49, and so on and so fourth). Both visuals are useing the same times. The values in the matrix are using a column called StudentID, and the values are as Count (Distinct).
I was asked to find a way to color the values on the right as follows:
- If the Value is above the 5 Staff number, then color it Green
- If the Value is below the 15 Staff number, then color it Red
- Ex: On January 25, 2022 the value is 30. This value would be Red
- If Value is in between, then color is Yellow
Here is an example of the data I am using
| Time | Date | StudentName | StudentID | College | CreditHours | 15 Staff | 5 Staff |
| 8:00 AM | January 27, 2022 | Joseph Mann | 005866 | Business | 14 | 49 | 51 |
| 9:00 AM | January 29, 2022 | John Doe | 447856 | Business | 16 | 77 | 79 |
| 9:30 AM | January 25, 2022 | Jane Doe | 555013 | Law | 18 | 70 | 72 |
| 10:00 AM | January 25, 2022 | Ryan Pierce | 554876 | Graduate | 16 | 61 | 63 |
| 8:00 AM | January 26, 2022 | Jannette Richardson | 332146 | Education | 15 | 49 | 51 |
| 12:00 PM | January 27, 2022 | Dameon Travis | 958741 | Business | 15 | 45 | 47 |
| 2:00 PM | January 28, 2022 | Marney Phillips | 004584 | Education | 15 | 63 | 65 |
I am not really 100% sure how this would be able to get formatted the way I am being asked. Would this be using Variables in a calculation? I struggle so much with variables and some DAX Functions. I've tried to do drop down on the values and conditionally format, however that really didn't work for my case.
I did attempt to do conditional formatting. However with the rules, I am not able to do conditional formatting based on both 15 Staff and 5 Staff. So it is being sort of odd with it because I am trying to compare the Value between both 15 and 5 Staff. Is there some sort of DAX Expression that can maybe be written to compare the value between both 15 and 5 Staff?
Anonymous,
You can achieve this with a measure that determines the color:
Color Measure = VAR vCount = [Count StudentID] VAR v15Staff = MAX ( Table1[15 Staff] ) VAR v5Staff = MAX ( Table1[5 Staff] ) VAR vResult = // use color names or hex codes SWITCH ( TRUE, vCount > v5Staff, "Green", vCount < v15Staff, "#E6113C", "Yellow" ) RETURN vResultCreate conditional formatting for the measure [Count StudentID]:
Hi, Anonymous
Based on the data you have provided, you can refer to the following methods.
Measure = Var N1=DISTINCTCOUNT('Table'[StudentID]) return SWITCH(TRUE(), N1>SELECTEDVALUE('Table'[5 Staff]),"Green", N1<SELECTEDVALUE('Table'[15 Staff]),"Red", "Yellow")Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous,
You can achieve this with a measure that determines the color:
Color Measure = VAR vCount = [Count StudentID] VAR v15Staff = MAX ( Table1[15 Staff] ) VAR v5Staff = MAX ( Table1[5 Staff] ) VAR vResult = // use color names or hex codes SWITCH ( TRUE, vCount > v5Staff, "Green", vCount < v15Staff, "#E6113C", "Yellow" ) RETURN vResultCreate conditional formatting for the measure [Count StudentID]:
Hi, Anonymous
Based on the data you have provided, you can refer to the following methods.
Measure = Var N1=DISTINCTCOUNT('Table'[StudentID]) return SWITCH(TRUE(), N1>SELECTEDVALUE('Table'[5 Staff]),"Green", N1<SELECTEDVALUE('Table'[15 Staff]),"Red", "Yellow")Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.