Forum Discussion
Having trouble creating dynamic goal
Hello! I'm having trouble with this specific issue, and it's not the first time I give up on having dynamic goals, because I can't get it to work, ever.
The current situation is as follows:
Partnr Table:
| PARTNR | NAME |
| PN1 | JOHN |
| PN2 | JAKE |
| PN3 | JONES |
Accoun Table:
| ACCOUN | PARTNR | CONSULTOR | SUPERVISOR |
| AC1 | PN1 | KATE | MARK |
| AC2 | PN1 | KATE | MARK |
| AC3 | PN1 | KATE | MARK |
| AC4 | PN2 | SUZY | MARK |
| AC5 | PN2 | SUZY | MARK |
| AC6 | PN2 | SUZY | MARK |
| AC7 | PN3 | LISA | CHRIS |
| AC8 | PN3 | LISA | CHRIS |
| AC9 | PN3 | LISA | CHRIS |
Action Table:
| ACTION_ID | ACCOUN | DATE |
| 1 | AC1 | 2025-01-01 |
| 2 | AC2 | 2025-01-01 |
| 3 | AC3 | 2025-01-03 |
| 4 | AC4 | 2025-01-03 |
| 5 | AC5 | 2025-01-05 |
Partnr - Accoun
PARTNR 1:* PARTNR
Accoun Action
ACCOUN 1:* ACCOUN
My goal:
I want to display a graph that shows two things for each day:
- The distinct count of PARTNR that received an action.
- The daily goal.
How the goal is calculated:
The goal should be 3 × the distinct count of CONSULTOR from the Accoun table for every weekday.
Why it needs to be dynamic:
If I select a specific SUPERVISOR in a slicer or visual, the goal should update to:
3 × the number of CONSULTOR assigned to that supervisor.
Additional requirement:
The graph should also show days where there were no actions, but still display the goal, so it’s clear when the goal wasn’t met.
Can someone guide me on the how to do this so I can finally be able to have goals on Power BI?
The intended output is:
Without any filters/slices applied, this should be the output (3 unique CONSULTOR times 3 = 9 for all days, actual is the distinct count of PARTNR for all ACCOUN that had at least one ACTION)
| DATE | GOAL | ACTUAL |
| 2025-01-01 | 9 | 1 |
| 2025-01-02 | 9 | 0 |
| 2025-01-03 | 9 | 2 |
| 2025-01-04 | 9 | 0 |
| 2025-01-05 | 9 | 1 |
If we are filtering for SUPERVISOR 'MARK', he only has 2 CONSULTORES, so 2 * 3 = 6 as our GOAL:
| DATE | GOAL | ACTUAL |
| 2025-01-01 | 6 | 1 |
| 2025-01-02 | 6 | 0 |
| 2025-01-03 | 6 | 2 |
| 2025-01-04 | 6 | 0 |
| 2025-01-05 | 6 | 1 |
PedroAzevedo here is what the measures looks like:
Goal = VAR __CountConusltor = DISTINCTCOUNT ( Accoun[CONSULTOR] ) RETURN __CountConusltor * 3 Action = CALCULATE ( DISTINCTCOUNT ( Accoun[PARTNR] ), CROSSFILTER ( 'Action'[ACCOUN], Accoun[ACCOUN], BOTH ) )
10 Replies
- parry2k
Super User
PedroAzevedo You explained it very well, kudos to you. Now, can you provide some example output based on what you explained. It will help immensely to provide an effective solution.
Sure, should've provided it from the start!
Without any filters/slices applied, this should be the output (3 unique CONSULTOR times 3 = 9 for all days, actual is the distinct count of PARTNR for the ACCOUN that had one ACTION)DATE GOAL ACTUAL 2025-01-01 9 1 2025-01-02 9 0 2025-01-03 9 2 2025-01-04 9 0 2025-01-05 9 1 If we are filtering for SUPERVISOR 'MARK':
DATE GOAL ACTUAL 2025-01-01 6 1 2025-01-02 6 0 2025-01-03 6 2 2025-01-04 6 0 2025-01-05 6 1
- parry2k
PedroAzevedo here is what the measures looks like:
Goal = VAR __CountConusltor = DISTINCTCOUNT ( Accoun[CONSULTOR] ) RETURN __CountConusltor * 3 Action = CALCULATE ( DISTINCTCOUNT ( Accoun[PARTNR] ), CROSSFILTER ( 'Action'[ACCOUN], Accoun[ACCOUN], BOTH ) )For some reason this is the output for my actual dataset when I selet 2 CONSULTOR from Accoun table, it only shows 6 as the goal for the days where both CONSULTOR had an ACTION.
- parry2k
PedroAzevedo I already shared the DAX, please test. Keep in mind I added a calendar table in the model and use that to visualize and this is what I did to generate the calendar table and the relationship:
Calendar = VAR __StartDate = MIN ( 'Action'[DATE] ) VAR __EndDate = MAX ( 'Action'[DATE] ) RETURN CALENDAR ( __StartDate, __EndDate ) - parry2k
PedroAzevedo In this case, I would recommend providing a little more example data to produce what you have shown. Maybe I missed something, the sample data was very limited hard to understand why it is not working.
I created a blank PBIX file and added some sample data to test your solution, and it works perfectly. The issue was caused by the relationship between the Calendar Date table and the Date field in the Action table being set to both directions, but the relationship line was hidden behind another table card in the Data Model (thanks to Power BI’s UI/UX).
Thank you so much for your patience and help!
- parry2k
PedroAzevedo, so you are trying to calculate the goal per day?
- parry2k
PedroAzevedo when you have selected MARK why action count is 0 on 2025-01-05
It was a typo, AC5 belongs to MARK too, so it should be 1, I edited the previous message.
And yes, I'm trying to get the daily goal, but it should be the same goal for every day (except for weekends but I can add a IF in the end of the measure to deal with that).
