Forum Discussion

Raj12's avatar
Raj12
Helper III
2 years ago

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

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)
 
Need Help
Show and Tell
Tips and Tricks

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

    • Raj12's avatar
      Raj12
      Helper III
      2 years ago

      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