Forum Discussion
Helper IICreate a colour measure for a table with multiple columns
Hi,
I have a table thats used each month to show a task is due on a certain day of the month, we input a number 3 if its still to be done 2 if its in progress and 3 when complete, I want to colour these red, orange and green but I cant see how to do it without going into each day individually and setting the conditional formatting, is there a way to do this?
Hi Belle2015,
The issue here is likely because the Day values in the DayTable not exactly matching the Day values in the unpivoted table.
- Go to Model view and make sure there is a active relationship between DayTable[Day] to the UnpivotedTable[Day] column. This should be a one-to-many relationship from DayTable to the unpivoted data.
- Make sure both tables have the exact same values in the Day column. For example if your unpivoted table has ‘10th’ then your DayTable must also contain ‘10th'. Check each day because even small mismatches will prevent the matrix from aligning correctly.
- Use DayTable[Day] in the Columns field and make sure ‘Show items with no data’ is enabled.
Once these are done the task statuses should appear correctly under their respective days.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
8 Replies
- SamWiseOwl
Super User
Hi Belle2015
First right click on a table in the Data pane and choose New Measure:
Format colour = --name of the measureSWITCH(TRUE() --test if the following conditions are met,SUM(Purchase[Price]) < 500, "Red" --your test and what colour to return,SUM(Purchase[Price]) < 1500, "Yellow","Green")Now select your matrix and go to the format settings:
Go to Cell elements and turn the background colour on:
Click the Fx symbol and change from Gradianet to FIeld value and then select your measure:
Enjoy the fruits of your labour:
Belle2015Hi,
Thanks for your response.
My issue is that the value comes in under the day it falls on so for this I would have to do the measure for each day so it would be creating 31 measures and this is what i was wanting to see if I can avoid, was just hoping there may be a way around this.
v-achippaCommunity Support
Hi Belle2015,
Thank you for reaching out to Microsoft Fabric Community.
Here the power bi requires separate conditional formatting setup per column. So we cannot use one measure to color multiple individual columns because formatting rules are basically column specific. Please follow below steps:
- Open Power Query Editor and select the 1st to 31st day columns. Now right click and choose Unpivot Columns. This will create two new columns, rename the Attribute to Day and Value to TaskStatus and then click Close & Apply.
- Use a Matrix visual to show the output and create a single DAX measure like this below:
TaskStatusColor =
SWITCH(
TRUE(),
SELECTEDVALUE('YourTable'[TaskStatus]) = 3, "#FF0000",
SELECTEDVALUE('YourTable'[TaskStatus]) = 2, "#FFA500",
SELECTEDVALUE('YourTable'[TaskStatus]) = 1, "#008000"
)
Use this single measure as a Field Value in conditional formatting for the matrix.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
