Forum Discussion

HenryJS's avatar
HenryJS
Post Prodigy
5 years ago
Solved

Matrix: IF Column and Status Colours

Hi all,

 

I would like to create two calculate columns shown below:

  • 'Vehicle Status' - IF MOT or Service = Week Ending THEN MOT or Service Date ELSE "In Use"
  • 'Works' - IF MOT or Service = Week Ending THEN "MOT" or "Service

 

I then want to create a measure which dictates colours of MOT or Service in Works in a matrix

 

 

Vehicle Reg NumberWeek Ending - TextService MOTVehicle StatusWorks
CXZ 800214/02/2021 14/02/202114/02/2021MOT
GXZ 291827/09/202001/09/202027/09/202027/09/2020Service
GXZ309201/03/2020  In Use 
NV19 YJA01/03/2020  In Use 

 

 

Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi HenryJS ,

     

    A little confusing, why the value of Works column for second row is "service"?

    And what's your martix looks like?

    Here's my formula for you reference.

    Vehicle Status = IF('Table'[Week Ending - Text]='Table'[Service ],'Table'[Service ],IF('Table'[Week Ending - Text]='Table'[MOT],'Table'[MOT]))

Works = IF('Table'[Week Ending - Text]='Table'[Service ],"service",IF('Table'[Week Ending - Text]='Table'[MOT],"mot"))

    And you could create a measure like 

    measure = IF(selectedvalue('table'[works])="mot",1,0)

    Then use conditional formatting:

    When measure =1 then "red", when meausre = 2 then "green".

     

    Best Regards,

    Jay

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    HenryJS , Try new columns like

    Vehicle Status - IF( [MOT] =[Week Ending] || [Service] = [Week Ending] , if([MOT] =[Week Ending] , [MOT] , [Service]) = [Week Ending] & "" , "In Use")
    Works = IF( [MOT] =[Week Ending] || [Service] = [Week Ending] , if([MOT] =[Week Ending] , "MOT" , "Service"),blank())

    • HenryJS's avatar
      HenryJS
      Post Prodigy
      5 years ago

      amitchandak this didn't work, is there a simpler way of restructuring the data that will allow me to bring more columns in at a later date?

       

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        HenryJS ,

        Try

        Vehicle Status - IF( [MOT] =[Week Ending] || [Service] = [Week Ending] , if([MOT] =[Week Ending] , [MOT] , [Service]) & "" , "In Use")
        Works = IF( [MOT] =[Week Ending] || [Service] = [Week Ending] , if([MOT] =[Week Ending] , "MOT" , "Service"),blank())

         

        For conditional formatting create a measure and use that "Field Value" option

  • lkalawski's avatar
    lkalawski
    Resident Rockstar
    5 years ago

    Hi HenryJS 

    Please use the code from amitchandak  to create calculated columns and then you should create a measure to adjust colors for different values in the Works column:

     

    Measure = 
SWITCH([Works M],
"MOT", "#FF0",
"Service", "#F0F"
)

     

    And use it in the conditional formatting section for the Works column:

    The Result:



    _______________
    If I helped, please accept the solution and give kudos! 😀

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi HenryJS ,

     

    A little confusing, why the value of Works column for second row is "service"?

    And what's your martix looks like?

    Here's my formula for you reference.

    Vehicle Status = IF('Table'[Week Ending - Text]='Table'[Service ],'Table'[Service ],IF('Table'[Week Ending - Text]='Table'[MOT],'Table'[MOT]))

Works = IF('Table'[Week Ending - Text]='Table'[Service ],"service",IF('Table'[Week Ending - Text]='Table'[MOT],"mot"))

    And you could create a measure like 

    measure = IF(selectedvalue('table'[works])="mot",1,0)

    Then use conditional formatting:

    When measure =1 then "red", when meausre = 2 then "green".

     

    Best Regards,

    Jay