Having trouble creating dynamic goal

[PedroAzevedo]

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:

1. The distinct count of PARTNR that received an action.
2. 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

[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.

[PedroAzevedo]

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 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 · Accepted solution]

@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 )
) 

[PedroAzevedo]

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 when you have selected MARK why action count is 0 on 2025-01-05

[PedroAzevedo]

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).

[parry2k]

@PedroAzevedo, so you are trying to calculate the goal per day?

[parry2k]

@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.

[PedroAzevedo]

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