Matrix using conditional formatting to dynamically display unique color or icon

[Dudditz]

Hello,

We have a scheduling app that stores it's data within dataverse.  Every schedule record has a relationship with an employee.  There are roughly 200 unique employees that use this application.

What I am looking to do is come up with a way to assign a unique color, or icon to every employee value, roughly 200 different values.  While the employee field is text I have also pulled in the employee number through a relationship.  The employee number is unqiue for every employee however I am still strugling how one might use conditional formatting to generate a different color or different color icon for the various employee records.  I have seen various posts of individuals using formulas and assigning a color per value which obviously would not be realistic to do this 200 times.  I was hoping there was a way to do this automatically similar to how chart's can auto-assign colors to the values.  Any guidance on this would be apprciated.

[jdbuchanan71 · Accepted solution]

You wouldn't be able to make it dynamic and still insure that an employees color was always the same so a fixed list would be the way to go.  But then you have to maintain it.  I would say you are better off just sorting it by employee last name, that way they will always be able to find themselves quickly in the list.

[Dudditz]

Hello JD, The purpose of the color is simply to help the employee visually pick out thier records quicker.  Of course it will indicate their name however if Brian is Green, or has a green icon it helps the employee identify their records quicker as Brian will always be green and he may have multiple scheduled tasks in the given month.  The following report will be displayed on various endpoints including 4k Tv's in dispatch areas, etc.  The report is rough and still in development however the pic should give you the idea.  An important item I forgot to mention while there is 200 employees with scheduled tasks this is across a dozen or so departments so technically the report will be filtered by department and any given department may have a max of 20 employees with scheduled tasks.  That would mean reuse of colors between departments would be just fine.  I am starting to consider mapping the employee records within the dataverse table to a color and use conditional formatting based upon field values.  I could create a dedicated table holding the color information as Mehdi Hammadi has a nice post found here

where he provides a csv of 148 colors he has tested and relate it the employee, map a unique color for the various employees and surface in Power BI.  Techinically I may only need to use around 20 of the colors however the benefit of this approach it would make the process a bit more manageable where you could change the color at the record if it did not look good.  Thoughts?  Am I over-engineering this?

[jdbuchanan71]

@Dudditz 

You could assign a rank to the employee based just on their employee ID and do a gradient off their rank.

Emoloyee Rank = RANKX(ALLNOBLANKROW('Table'[Emloyee ID]),CALCULATE(SELECTEDVALUE('Table'[Emloyee ID])),,ASC)

The problem will be that, with 200 values, you will have colors so similar they would be indistinguishable from each other:

Can I ask why you are trying to assign a unique color to each employee?  What is the purpose of the color?