Forum Discussion

Belle2015's avatar
Belle2015
Icon for Helper II rankHelper II
1 year ago
Solved

Create 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 ...
Need Help
  • v-achippa's avatar
    v-achippa
    1 year ago

    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

SamWiseOwl's avatar
SamWiseOwl
Icon for Super User rankSuper User
1 year ago

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's avatar
    Belle2015
    Icon for Helper II rankHelper II
    1 year ago

    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's avatar
      v-achippa
      Icon for Community Support rankCommunity Support
      1 year ago

      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's avatar
        Belle2015
        Icon for Helper II rankHelper II
        1 year ago

        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 🙂