Create a colour measure for a table with multiple columns

[Belle2015]

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? 

[Belle2015]

Thank you @v-achippa I have got it sorted now and as I am using the day from the new day table I dont need to do add the sort column, I just changed it so as the dates dont have the "th" after them and just appear as the number. 

Thanks for all your help with this 🙂

[SamWiseOwl]

Hi @Belle2015 

First right click on a table in the Data pane and choose New Measure:

Format colour = --name of the measure

 SWITCH(

    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:

[Belle2015]

Hi, 

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-achippa]

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

[Belle2015]

Hi @v-achippa , 

This works great thanks, 

The only thing I would like the days to be in order and I cant see how to do this?

I would also like it to show days even when there is no task? 

I selected the show items with no data but they still do not show 

 Any help is great appreciated 🙂

[v-achippa]

Hi @Belle2015,

After unpivoting power bi sorts the Day column alphabetically so to sort it properly, in Power Query Editor after unpivoting select the Day column and go to Add Column --> Custom Column and enter the below formula to extract the day number:

= Number.FromText(Text.Select([Day], {"0".."9"}))

Name this new column as DaySort and click Close & Apply.

Now in power bi go to Table view, select the Day column, go to the Column tools --> Sort by column and select DaySort. Now the days will be sorted from 1st to 31st correctly in the matrix visual.

For showing all days, go to Modeling --> New Table and paste this below code:

DayTable =

ADDCOLUMNS (

    CALENDAR (DATE(2025, 1, 1), DATE(2025, 1, 31)),

    "Day", FORMAT([Date], "d") & "th"

)

Create a relationship between DayTable[Day] and your unpivoted table’s Day column.

In the Matrix visual use DayTable[Day] in the Columns field and ensure Show items with no data is enabled.

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

[Belle2015]

Hi @v-achippa , 

The sort part of this works perfectly thanks. 

The only thing is when I have set up the table and used the Day in the column it is not bringing in the task status under the day, it just has them under a blank column at the begining.

Thanks for your help with this 🙂

[v-achippa · Accepted solution]

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