Color coded based on conditions

I have Sample data as below
Empid OverallRating Apraiseratinglastyr Apraiseratingcurrentyer
AAAAAA 3.5 3 4
BBBBBB 4 4 4

Output willbe as below
Empid Status OverallRating Apraiseratinglastyr Apraiseratingcurrentyer
AAAAAA 3.5 3 4
BBBBBB 4 4 4
Status is color coded based on OverallRating<5 Color is RED
Status color coded based on >Apraiseratinglastyr and lessthan orequal Appraisalratingcurrentyer color is Blue(this is sample scenario non )

I need the above output can you please help me on this.

Hi @amruthdna2018

Could you please help to provide sample file with expected output.

I think we have similar solution in another post. Please help to look for this it might help you to achieve your result.

I am uploading another example with sample data .Please find attached file SampleData.xlsx (11.1 KB)

Kindly do needful

Hi @amruthdna2018,

Could you please help to clarify on the below.

SafeSideqty but less than minlot — here you mean CurrentQty>SafeSideqty and CurrentQty< minlot??
If yes then it is not matching with the yellow.

Thanks
Mukesh

Yes by mistake color got selected…for yello but logic is same…yellor or green some color we need to show may be my data doesnt have required creteria but conditions are as above

Hi @amruthdna2018

You can use the below DAX code to create a measure which will be used to conditional formatting . You can change your condition per your requirement.

ColorcodeValue = 
SWITCH( 
    TRUE(),
    SELECTEDVALUE( 'Table'[CurrentQty]) <=SELECTEDVALUE('Table'[Safesideqty]), -1,
    SELECTEDVALUE('Table'[CurrentQty]) >SELECTEDVALUE('Table'[Safesideqty]) && SELECTEDVALUE('Table'[CurrentQty]) < SELECTEDVALUE('Table'[Minlot] ),1
) 

Steps to format:-

  1. Select the table and goto Format.
  2. Select “Conditional Formatting”
  3. Select "Status from the drop down( the column on which you want to do formatting)
  4. Turn On the Background Color
  5. Click on “Advanced Controls”

image

  1. Have the below setup and click ok.

The output.

Hope this helps you.

Thanks
Mukesh

1 Like

Thanks mukesh, I hope it helps me …let me try and come back

Hi @amruthdna2018, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Hi Mukesh,

Can i Use Above Status Collumn as Slicer or filter in my report so that i can select the Color and change the data .Can you please help me on this

Thanks
Amruth

Hi @amruthdna2018,

Can you please attach the report once again with the solution as i can’t find exact solution file in my system ( may be updated with your new request)

In the above example the colorcodedValue column is a measure which i have written formula as below:

ColorcodeValue =
SWITCH(
TRUE(),
SELECTEDVALUE( ‘Table’[CurrentQty]) <=SELECTEDVALUE(‘Table’[Safesideqty]), -1,
SELECTEDVALUE(‘Table’[CurrentQty]) >SELECTEDVALUE(‘Table’[Safesideqty]) && SELECTEDVALUE(‘Table’[CurrentQty]) < SELECTEDVALUE(‘Table’[Minlot] ),1
)

After which i configured in Conditionalformatting and assign the rules to these number -1 and 1 and used in the rpeort.The report column is showing Red(-1) Grren(1) as per the logic.Now i wanted to use this ColorcodedValue as Slicer or filter is this possible.

Kindly do needful.

Thanks & Regards
Amruth

Hi @amruthdna2018

To use it as a slicer or filter in the report you need to use as calculated column not measure. Also if you convert above DAX measure to calculated column then will not have the same dynamic as it doesn’t calculate on the fly. Still you can give a try and see how is the result.

Thanks
Mukesh