topic Re: Return Value from Tbl2 where Tbl1.Value is between two Tbl2.Values in DAX Commands and Tips

Return Value from Tbl2 where Tbl1.Value is between two Tbl2.Values

PBI_Inquisitor — Tue, 24 May 2022 07:58:52 GMT

I have two tables T1 & T2

T1 has

CustomerID	MonthID	Indicator from T2
12345	202301	0
23456	202301	1
34567	202301	0 (Returns 0 as 202301 is NOT between the start and end date in T2

The Indicator From T2 should return when the T1.MonthID is between the start and end months in T2

T2:

CustomerID	StartMonth	EndMonth	Indicator
12345	202301	202302	0
12345	202302	202304	0
12345	202303	202305	0
23456	202301	202302	1
34567	202302	202304	1

Tried various thing and either get #ERROR or no values returned

Any advice on best approach please?

Thanks in advance

Re: Return Value from Tbl2 where Tbl1.Value is between two Tbl2.Values

tamerj1 — Mon, 23 May 2022 18:56:35 GMT

Hi PBI_Inquisitor

I assume you can build one to one relationship between the two tables. Then you can create a new calculated column in T1

Indicator from T2 =
IF (
T1[MonthID] >= RELATED ( T2[StartMonth] )

&& T1[MonthID] <= RELATED ( T2[EndMonth] ),

)

Re: Return Value from Tbl2 where Tbl1.Value is between two Tbl2.Values

PBI_Inquisitor — Tue, 24 May 2022 08:00:07 GMT

Thanks for your reply tamerj1 but unfortunately I cant build a 1:1 relationship in this instance - I've updated the Table 2 to show this

Re: Return Value from Tbl2 where Tbl1.Value is between two Tbl2.Values

tamerj1 — Tue, 24 May 2022 08:42:20 GMT

PBI_Inquisitor
The use

Indicator from T2 = VAR CurrentCusomer = T1[CustomerID] VAR CurrentMonth = T1[MonthID] VAR T2Start = CALCULATE ( MAX ( T2[StartMonth] ), T2[CustomerID] = CurrentCusomer ) VAR T2End = CALCULATE ( MAX ( T2[EndMonth] ), T2[CustomerID] = CurrentCusomer ) RETURN IF ( CurrentMonth >= T2Start && CurrentMonth <= T2End, 1, 0 )

Re: Return Value from Tbl2 where Tbl1.Value is between two Tbl2.Values

PBI_Inquisitor — Tue, 24 May 2022 11:23:55 GMT

Thank you so much for your help tamerj1 - much appreciated