Forum Discussion

regnidem's avatar
regnidem
Frequent Visitor
7 years ago
Solved

Conditional formatting comparing values across different tables

Hello all,

 

What I'm trying to accomplish:

 

Essentially I'm trying to create a matrix visualization with conditional formatting for the cells.  However, it involves comparing values across different tables.  Creating the matrix is easy; I've already done that.  It's the conditional formatting that I'm having trouble with.

 

I have two tables.  A 'Services' table that has a employee ID, an enounter ID (more than one service can be performed per encounter) and a date (everything has been normalized to the first day of the month).  See spoiler:

 

 

Spoiler
IDEncounter IDYearMonth
1237411/1/2018
1237411/1/2018
1238421/1/2018
1238421/1/2018
1238421/1/2018
1238421/1/2018
1239531/1/2018
1236542/1/2018
1237893/1/2018
1232533/1/2018
4562531/1/2018
4567531/1/2018
4568531/1/2018
4567531/1/2018
4561572/1/2018
4561492/1/2018
4563672/1/2018
4569542/1/2018
4567563/1/2018
4569313/1/2018
7895841/1/2018
7895261/1/2018
7892541/1/2018
7892561/1/2018
7899852/1/2018
7891252/1/2018
7893252/1/2018
7896523/1/2018
7894523/1/2018
7894583/1/2018

 

 

I have a second table that lists the monthly goals for number of encounters for each employee

 

IDGoal
1232
4563
789Ineligible

 

What I need to do is count the number of distinct encounters per employee per month.  Then, if the count of distinct encounters is higher than the goal, highlight that cell.  An approximate visualization using the above data is shown.

 

 

 

 

What I've done so far:

 

While looking at other threads trying to cobble together a solution, I think I need to create two new measures.  The first counting the  number of distinct encounter IDs per month and the second comparing that count to the goals for each employee.

 

I was able to create the first measure:

 

NumberHours = CALCULATE(
              DISTINCTCOUNT(Services[Encounter ID]), FILTER(Services, Services[YearMonth]
))

I'm stuck on the second measure:

 

GoalMet = CALCULATE(
                  if(Services[NumberHours] > BonusGoals[Goals], 1, 0)
)

Once I have this measure, I think I can make a conditional formatting statement using the GoalMet measure to highlight the cells, but I'm not positive.

 

Am I on the right track?  Is this something that's possible to accomplish in Power BI?

Need Help
  • MarkLaf's avatar
    MarkLaf
    7 years ago

    Yes, you are on the right track and basically there.

     

    I think the main issue is that because you have text in your BonusGoals[Goal] column, it can't be recognized as a number column to compare against number of encounters. I would either remove that row or make the goal null, and change format to number, although the below measure should work with your data as is (IFERROR + VALUE are handling the text):

     

    GoalMet = 
    CALCULATE(
        VAR CurGoal = IFERROR(VALUE(SELECTEDVALUE(BonusGoals[Goal])),BLANK()) RETURN
        IF([Encounters]>= CurGoal && ISNUMBER(CurGoal),1,0),
        Services
    )

     

     

     

     

    P.S. as Greg_Deckler also mentions, your NumberHours measure can just be DISTINCTCOUNT(Services[Encounter ID])  -- no need for the calculate or filtering, since the dates and encounter IDs are in the same table so relationship between encouters and dates are implicitly known.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Get rid of the CALCULATE and probably use LOOKUPVALUE to grab your Goal.

  • MarkLaf's avatar
    MarkLaf
    Super User
    7 years ago

    Yes, you are on the right track and basically there.

     

    I think the main issue is that because you have text in your BonusGoals[Goal] column, it can't be recognized as a number column to compare against number of encounters. I would either remove that row or make the goal null, and change format to number, although the below measure should work with your data as is (IFERROR + VALUE are handling the text):

     

    GoalMet = 
    CALCULATE(
        VAR CurGoal = IFERROR(VALUE(SELECTEDVALUE(BonusGoals[Goal])),BLANK()) RETURN
        IF([Encounters]>= CurGoal && ISNUMBER(CurGoal),1,0),
        Services
    )

     

     

     

     

    P.S. as Greg_Deckler also mentions, your NumberHours measure can just be DISTINCTCOUNT(Services[Encounter ID])  -- no need for the calculate or filtering, since the dates and encounter IDs are in the same table so relationship between encouters and dates are implicitly known.