Forum Discussion

Stephane1974's avatar
Stephane1974
New Member
3 years ago
Solved

Link between 3 tables for a new column

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

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

3 Replies

  • Stephane1974's avatar
    Stephane1974
    New Member
    3 years ago

    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

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.