Is it possible to use conditional formatting on row and value?

[Reine]

Hi all - I have a matrix that calculates our employees daily "points" and have conditional formatting set up to show a check mark when the daily goal is reached.  The issue is that some of the employees have different daily goals because they are part time.  Is there a way set up conditional formatting based on employee and point value?

Attached is an example visual of a day that shows a handful of employees have reached the goal I have set (24) - but Carlos and Destiny have also reached their goal of 12 and Josh has reached his goal of 15 so I want check marks to show up for them too.

[v-eachen-msft]

Hi @Reine ,

This is my sample file.

The "Goal" is the goal of everyone. The "column" is a new column to check if this employee reached the goal.

Column =
IF ( Table2[Points] >= Table2[Goal], 1, 0 )

Then turn to the Conditional Formatting tab.

Here is the result.

[Reine]

Thank you @v-eachen-msft - this is basically what I am trying to do, but running into some problems. 

I think it's because my points are calculated by a measure in the "appointments" table.  The "appointments" table is connected to the "user data" table.  Those two tables are brought in from my SQL database.  I made a new "user goals" table that is an excel sheet and brought it in.  It is connected to the "user data" table. 

I've tried adding a column to calculate the users goals met, as you indicated,  to the "user data" table and also tried the "user goals" table but its not calculating accurately in either place.  I think it's because I have each user "grouped" so that the points from both locations they worked are added together in my visual.  But even when I try to add the column to a user data table that isn't grouped it doesn't seem to be using my date filter so it's kicking back info based on all points rather than just the day I am looking at.

I also tried to add a column to my "user goals" table to bring the points into it -   Points = AppointmentsT[Count Points Room]  

but that seems to bring in all points ever instead of just the day or week that I am looking at too.

[Anonymous]

Hi, Reine.

Did you think about normalize your data on your metric and enrich that metric with your specific points and User Rules? If so, just aply your conditional formating as you have done earlier.

Did I help you to go trough this problem?

Cheers.

Samyr A. Moises

Power Platform Specialist

[Reine]

I'm very new to this so I don't know what is meant by "normalize" - but I will do some researching 🙂  thanks for your reply

[PBFlash]

Another option would be to create a calcualted column that could return yes/no whether their goal was met. Then use the new column for your conditional formatting rule. 

[Reine]

thank you for your reply.  I really am very new to Power Bi and though I know how to add a column I'm not understanding how to calculate this.  My points are calculated with a measure and I tried this by adding a column using an IF statement:

Goal Met = IF(RoomAppointmentsT[Count Points Room] >=24, "yes", "no")

but it just kicks back no for everyone - even those that have over 24 points

But even if it did work, I'm unsure how I would caluclate it seperately for everyone - multiple IF statements for each person?

[Anonymous]

@Reine, let me try to help you!

Is that possible to create a table with user Details? If so, create it with user classification adding on it the Daily Goal.

Just like this:

Sample Table: UserClassification

UserClassId UserClass UserDailyGoal

1                          FullTIme        24

2                          PartTime       16

And then, add somehow the UserClassId on your current User Table

Table: Your One

UserDataFields.....         UserClassId

John Doe, M, Marri...,...     1

Marie Chan, F, Sing...,...      2

.....(your full dataset )

And then, dynamize your metric using the field UserDailyGoal from your new table:

Goal Met = IF(RoomAppointmentsT[Count Points Room] >=UserClassification[UserDaily], "yes", "no")

Something like this could help you to start your solution....after that it will be possible to insert goal parameters and change de goal depending on dates or other data.

Ps.: Do not forget to make de relationship true to the new table UserClassification and your current user table.

Hope to helped you a bit!

[Reine]

Thank you.  I feel like this could work but I am running into some issues that I'm not sure how to resolve. 

In my main user data table the users are listed more than once with different user ID's for each location they work in.  For example:

User ID    Name

123          Taylor M - MD

456          Taylor M - GP

321          Bryan R - MD

654          Bryan R - GP

789          Carlos S - MD

246          Carlos S - GP

In the visual I linked earler, I want to show total points regardless of where they worked, so I grouped each employees locations together and named each group with their full name.  So now my data table looks like this

User ID    Name                           Name (Groups)

123          Taylor M - MD             Taylor Moore

456          Taylor M - GP              Taylor Moore

321          Bryan R - MD               Bryan Reynolds

654          Bryan R - GP                Bryan Reynolds

789          Carlos S - MD              Carlos Smith

246          Carlos S - GP               Carlos Smith

I made a seperate table as you indicated with daily, weekly, and monthly goal columns, loaded it and created a relationship between it and my employee data table with the user ID but it was doubling all goals when I pulled it into my visual.  I then tried creating a new table with the goals and just the group names instead of user ID's, loaded and created the relationshp between the Name (group) field in both tables.  

My new table looks like this:

Name (Groups)       Time Class          Daily Goal      Weekly Goal      Monthly Goal

Taylor Moore          Full time                   24                   120                    480

Bryan Reynolds       Part time 1               17                     84                    336

Carlos Smith           Part time 2                14                     72                    288

This kicks back the correct goals now, but I am not sure how to bring this info into my main user data table as you indicated.  I thought maybe I could merge the two tables in query editor, but the Name (Groups) column does not show up as a column in power query so I have nothing to connect them with.

I tried making a new measure with the DAX you supplied, but it doesn't let me use data from the new Tech Goals table I made along with the current user data table.

I appreciate your help with this - I've likely not gone about things in the most efficient way as I'm learning 🙂 

[Anonymous]

Hey man, to simplify that, use a Min function....there are some other elegant and eficient ways to solve that, but...let's see:

Let's try something....We know that this relationship will return duplicate values, if your dataset is not that big, try to use min(dailyGoal) instead of any other functions on your goal measure..

Check if really helps you!

If don't.... is that possible to share your PBIX than I can send you a solution back!?

Cheers!

[Reine]

thank you so much for your help.  I still can't quite seem to figure this out 😞 

Unfortunately, I can't send my pbix file because it contains patient information and I would be violating HIPAA laws.

I feel like I am almost there but because my understanding of Power Bi is low I think I'm not making a connection somewhere.  I changed it to show Min for Daily Goal and it shows the proper goal now but I still can't figure out how to make a measure to show  "yes" they reached the goal or "no" they didn't.  When I try to use the DAX with the IF statment you gaveit won't let me use the points data with the daily goal values becuase they are in different tables.  I tried to use RELATED to make it work but that doesn't work either.