cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Establish Many to 1 Relationship Between Tables Based on Date Value

I have 2 tables as data sources which I brought it into PBI desktop via separate SQL queries, each query points towards a different database.  

I want to create an invoice dashboard which includes:

From Table_1: InvoiceID_Unique, IdSponsor, InvoiceDate

and from Table_2: IdPartner

 

I want my dash to show the IdPartner which was assigned at the time the invoice was issued.   In Table_2, the IdPartner associated with IdSponsor for any given time period is defined by the BeginDate and EndDate.  

 

Example:  Invoice_UniqueID 1 was issued to IdSponsor 1234 on 1/1/2019, and Table_2 shows that at that time IdSponsor 1234 was affiliated with IdPartner 1.  Table_2 EndDate defines when a relationship between IdPartner and IdSponsor ended, if EndDate is NULL then the relationship is current.

 

Any thoughts on how to accomplish this?  Thanks!

 

Table_1:

InvoiceID_UniqueIdSponsorInvoiceDate
112341/1/2019
212342/15/2019
3356711/8/2019
423454/15/2019
523456/15/2019

 

Table_2:

IdSponsorIdPartnerBeginDateEndDate
1234110/1/20182/1/2019
123422/2/2019NULL
3567110/5/2019NULL
2345321/1/20155/1/2019
2345455/2/2019NULL

 

Desired Result:

InvoiceID_UniqueIdSponsorInvoiceDateIdPartner
112341/1/20191
212342/15/20192
33456711/8/20191
423454/15/201932
523456/15/201945

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Hi @Red11 ,

 

look at this.

PBIX

 

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Refer, the way current employee has been created in the same way it can be doen

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Outherwise in you invoice table create one measure

minx(filter(table2,table1[col1] =table2[col1] && table1[col2] =table2[col2]),table1[col4])

while giving condition on close use isblank for null value

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

Hi @Red11 ,

 

look at this.

PBIX

 

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

View solution in original post

Highlighted

Having difficulty creating the measure, table 1 isn't recognized in the formula, though table 2 is.   I'm trying to enter the formula in data view, is that the correct spot?  

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors