Measure to return value from a connected table based on another table selection

[Raj12]

Table 1 (LFSLTRNR) has monthly sales for an agent

Table 2 (Agents Target) has monhtly target for an agent.

Table 1 and 2 are connected via agent code

Report has Sale date filter from table1 and measure showing agent target for that slected month.

If there is no sale for an agent in a month then their target is coming as blank in the measure, but I want to show target even if there is no sale data in table 1 for an agent i.e. ignoring connection between two table when there is no sale.

Measure=

var yrs = SELECTEDVALUE(LFSLTRNR[STRNDT_YEAR])

var mon_start_date =     CALCULATE(Min(LFSLTRNR[STRNDT_Month] ),ALLSELECTED( LFSLTRNR[STRNDT_Month] ))

var mon_end_date =     CALCULATE(Max( LFSLTRNR[STRNDT_Month] ),ALLSELECTED(LFSLTRNR[STRNDT_Month] ))

var agent = SELECTEDVALUE('Team Details (Prop track)'[Sales Code])
return

CALCULATE(SUM('Agents Target'[Value]),
'Agents Target'[Sales Code]=agent,
'Agents Target'[Month]>=mon_start_date && 'Agents Target'[Month]<=mon_end_date && 'Agents Target'[Year]=yrs)

[lbendlin]

That would be a question for your data model. How does that look like?

[Raj12]

Hi Ibendlin,
Please find below data model:

Agent Code & Sales Code of two table are connected together.
Now if we select STRNDT Feb 2024, there is no sale for Agent PAG but there is target for him, hence want measure to show target even if there is no sale for an individual.
Please suggest on the same. Thank you

[lbendlin]

Is that bidirectional *:* ?

[Raj12]

yes its bi-directional

[lbendlin]

that's the worst case scenario.  Correct it if possible or use CROSSJOIN(..,NONE) to temporarily disable the relationship.

[Raj12]

Can you please suggest on this?

[Raj12]

yes can change to below: