Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jeronimo2334
Helper III
Helper III

Joining tables on multiple date columns

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.

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

11 REPLIES 11
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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
Super User
Super User

@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.  

 

 

parry2k_0-1616462787663.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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
Helper III
Helper III

bump

jeronimo2334
Helper III
Helper III

bump

jeronimo2334
Helper III
Helper III

bump

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

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
Helper III
Helper III

bump

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors