Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello all!
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:
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?
Solved! Go to Solution.
I'm sort of confused a little on this. So do I create a DAX Expression using both the 15 Staff and 5 Staff?
SWITCH(TRUE()
[15 Staff] < #, "Red",
[5 Staff] > #. "Green",
ELSE "Yellow"
Hi @PrivateAnalytic ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a measure as below:
Conditional formatting =
VAR _seltime =
SELECTEDVALUE ( 'Time'[Time] )
VAR _15staff =
CALCULATE (
MAX ( 'Time'[15 Staff] ),
FILTER ( 'Time', 'Time'[Time] = _seltime )
)
VAR _5staff =
CALCULATE (
MAX ( 'Time'[5 Staff] ),
FILTER ( 'Time', 'Time'[Time] = _seltime )
)
RETURN
SWITCH (
TRUE,
[Count of Students] < _15staff, "Red",
[Count of Students] > _5staff, "Green",
[Count of Students] >= [Count of Students]
&& [Count of Students] <= _5staff, "Yellow"
)
2. Apply conditional formatting for the Values field
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
@PrivateAnalytic , You can create a color measure based on the condition that should return color and that you can use in conditional formatting using field values
example
Switch(true(),
[SLA] = 1 , "Green" ,
[SLA] >= .5 , "Amber" ,
"Red "
)
)
I'm sort of confused a little on this. So do I create a DAX Expression using both the 15 Staff and 5 Staff?
SWITCH(TRUE()
[15 Staff] < #, "Red",
[5 Staff] > #. "Green",
ELSE "Yellow"
User | Count |
---|---|
30 | |
18 | |
15 | |
14 | |
10 |