Joining tables on multiple date columns

[jeronimo2334]

Hello,

I have table1 and table2 and I'm hoping to get table3 as a result.
https://www.dropbox.com/sh/wn450alvxn6kja5/AACsmmOXBXs94pXtbDxoyFs_a?dl=0

Some information on how this calculation is supposed to work:
A) The tables join on the id column.
B) The datePosted column on table2 is less or equal of each date column in table1 that has the Start prefix.
C) The datePosted column on table2 is (greater or equal) or null of each date column in table1 that has the Thru prefix.

Thanks for your time.

[parry2k]

@jeronimo2334 I think I was going to use it but decided not to, good question, you can remove that line. 

[parry2k · Accepted solution]

@jeronimo2334 here is the DAX expression, add as a new column. I didn't create the relationship between the tables to avoid "many to many" but that can be used too:

Name = 
VAR __id = table2[id]
VAR __post = table2[datePosted]
VAR __relatedTable = CALCULATETABLE ( table1, TREATAS ( { __id } ,  table1[id] ) )
VAR __names = 
CALCULATETABLE ( 
    VALUES ( table1[givenName] ), 
    TREATAS ( { __id }, table1[id] ),  
    table1[crStart] <= __post , 
    ( table1[crThru] == BLANK() || table1[crThru] >= __post  ),
    table1[cLStart] <= __post , 
    ( table1[cLThru] == BLANK() || table1[cLThru] >= __post  ),
    table1[opiStart] <= __post , 
    ( table1[opiThru] == BLANK() || table1[opiThru] >= __post  ),
    table1[piStart] <= __post , 
    ( table1[piThru] == BLANK() || table1[piThru] >= __post  ),
    table1[uiStart] <= __post , 
    ( table1[uiThru] == BLANK() || table1[uiThru] >= __post  ) 
)

RETURN
CONCATENATEX (
    __names,
    [givenName], "," 
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[jeronimo2334]

Thanks a lot @parry2k , that did the trick. 

Not urgent, but when you get some time, can you explain the logic behind your solution. 
I dont understand where __relatedTable is being used.

[parry2k]

@jeronimo2334 the logic you provided doesn't make sense to me based on the output you shown in table3. Taken an example of ID = 1, date posted is in the year 2015, 2018, and 2019 and the crStart for this id has only date in the year 2013, it means this condition will fail (table2.datePosted <= table1.crStart ) and it means you will get no name for Id = 1,  although, in your output, you have shown name for Id = 1, (Sherry and Maurine), something doesn't seem to be right, either logic or data you provided is wrong, or I'm missing something.  

[jeronimo2334]

Thanks for looking into this. I had the logic wrong.
A) The tables join on the id column.
B) All the table1 Start dates should be <= table2.datePosted.
C) All the table1 Thru dates should be null or >= table2.datePosted.

This is how the query looks like in MySQL:
( table1.crStart <= table2.datePosted AND (table1.crThru IS NULL OR table1.crThru >= table2.datePosted)) AND 
( table1.clStart <= table2.datePosted AND (table1.clThru IS NULL OR table1.clThru >= table2.datePosted)) AND
( table1.opiStart <= table2.datePosted AND (table1.opiThru IS NULL OR table1.opiThru >= table2.datePosted)) AND 
( table1.piStart <= table2.datePosted AND (table1.piThru IS NULL OR table1.piThru >= table2.datePosted)) AND 
( table1.uiStart <= table2.datePosted AND (table1.uiThru IS NULL OR table1.uiThru >= table2.datePosted))

[jeronimo2334]

bump

[jeronimo2334]

bump

[jeronimo2334]

bump

[v-easonf-msft]

Hi， @jeronimo2334 

The information you have provided is not making the problem clear to me.I have check the table3. I'm still confused with the result you want. 

Can you please explain with a more detailed demo? 

Best Regards,
Community Support Team _ Eason

[jeronimo2334]

Sure, so in order to arrive to that result (table 3) we first join table1 & table 2 on id.

Then in order to figure out which givenName from table1 should be used in table3 we make a date comparison of the datePosted from table2 against the dates in table1. 

They way that comparison works is (taken from MySQL):
(table2.datePosted <= table1.crStart AND (table1.crThru IS NULL OR table2.datePosted >= table1.crThru )) AND 
(table2.datePosted <= table1.clStart AND (table1.clThru IS NULL OR table2.datePosted >= table1.clThru)) AND
(table2.datePosted <= table1.opiStart AND (table1.opiThru IS NULL OR table2.datePosted >= table1.opiThru)) AND 
(table2.datePosted <= table1.piStart AND (table1.piThru IS NULL OR table2.datePosted >= table1.piThru )) AND 
(table2.datePosted <= table1.uiStart AND (table1.uiThru IS NULL OR table2.datePosted >= table1.uiThru ))

I tried using LOOKUPVALUE but that doesn't allow you to use expressions to arrive to the desired result.
Also, the reason why some cells have two names in table3 is because there were mutliple records matching the criteria so the names were concatinated. 

[jeronimo2334]

bump