Matrix: IF Column and Status Colours

[HenryJS]

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 Number	Week Ending - Text	Service	MOT	Vehicle Status	Works
CXZ 8002	14/02/2021		14/02/2021	14/02/2021	MOT
GXZ 2918	27/09/2020	01/09/2020	27/09/2020	27/09/2020	Service
GXZ3092	01/03/2020			In Use
NV19 YJA	01/03/2020			In Use

[Anonymous · Accepted solution]

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

[lkalawski]

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! 😀

[amitchandak]

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

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

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

@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