Link between 3 tables for a new column

[Stephane1974]

Hi Guys

here my issue. I have three tables where the key is based on an interval. 

table1: columns :

tbl1Dim2

tbl1Dim3

tblResults

table 2 : columns :

tbl2Dim2_StartInterval

tbl2Dim2_EndInterval

tbl2Results

table3: columns:

tbl3Dim2

tbl3Dim3

tbl3Results

I would like to show in table 1 tbl1Result :

if tbl1Dim3 in between tbl2Dim3StartInterval and tblDim3EndInterval = tbl2Result

if Concatenate(tbl1Dim2, tbl1Dim3) =Concatenate(tbl3Dim2, tbl3Dim3) then  tbl1Result=tbl3Result

hope I am clear enough.

thanks

Stephane

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[v-jianboli-msft · Accepted solution]

Hi @Stephane1974 ,

Based on your description, I have created a simple sample:

Please try:

tblResults = 
var _a = FILTER('table2',[tbl2Dim3_StartInterval]<=[tbl1Dim3]&&[tbl1Dim3]<=[tbl2Dim3_EndInterval])
var _b = FILTER('table3',CONCATENATE([tbl3Dim2],[tbl3Dim3])=CONCATENATE([tbl1Dim2],[tbl1Dim3]))
return SWITCH(TRUE(),
COUNTROWS(_a)>=1,MAXX(_a,[tbl2Results]),
COUNTROWS(_b)>=1,MAXX(_b,[tbl3Results]))

Final output:

Best Regards,

Jianbo Li

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

[Stephane1974]

With a minor correction

instead of 

if tbl1Dim3 in between tbl2Dim3StartInterval and tblDim3EndInterval = tbl2Result

I thought

if tbl1Dim3 in between tbl2Dim3StartInterval and tblDim3EndInterval then tbl1Result = tbl2Result