topic Measure returning value from a connected table ignoring other table selection in DAX Commands and Tips

Measure returning value from a connected table ignoring other table selection

Raj12 — Fri, 21 Jun 2024 09:41:44 GMT

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)

Re: Measure returning value from a connected table ignoring other table selection

lbendlin — Sat, 22 Jun 2024 14:46:28 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Measure returning value from a connected table ignoring other table selection

Anonymous — Mon, 24 Jun 2024 03:09:03 GMT

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi Raj12 ,

Do you want to display the data that table LFSLTRNR shows as null values in measure.

You can click to show items with no data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Measure returning value from a connected table ignoring other table selection

Raj12 — Fri, 28 Jun 2024 11:33:34 GMT

Hi, Thank you for your response. Measure is not working for the agent where there is no sale eg. for PAG:

This is the issue that I was facing where agent target is not showing when there is no sale in a month for an agent. Please help on this. Thanks

Re: Measure returning value from a connected table ignoring other table selection

Raj12 — Fri, 28 Jun 2024 11:42:40 GMT

PFB data:
Agents Target Table

Year	Month	Sales Code	Value
2024	1	Agent	1515
2024	1	Rax	4545
2024	2	Aga	2323
2024	2	Agent	1316
2024	2	Pag	1212
2024	2	Rax	6767
2024	3	Agent	1751

LFSLTRNR

Agent Code	Strndt	Strndt_Month	Strndt_Year
AGA	13/02/2024 00:00	2	2024
Agent	11/01/2024 00:00	1	2024
Agent	13/02/2024 00:00	2	2024
Agent	14/03/2024 00:00	3	2024
Rax	11/01/2024 00:00	1	2024