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 E...
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

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

HenryJS's avatar
HenryJS
Post Prodigy
5 years ago

amitchandak how can I add another column in? for example if I wanted to add another date field for 'Last Service'

 

thank you for your help