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.
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_Unique | IdSponsor | InvoiceDate |
1 | 1234 | 1/1/2019 |
2 | 1234 | 2/15/2019 |
3 | 3567 | 11/8/2019 |
4 | 2345 | 4/15/2019 |
5 | 2345 | 6/15/2019 |
Table_2:
IdSponsor | IdPartner | BeginDate | EndDate |
1234 | 1 | 10/1/2018 | 2/1/2019 |
1234 | 2 | 2/2/2019 | NULL |
3567 | 1 | 10/5/2019 | NULL |
2345 | 32 | 1/1/2015 | 5/1/2019 |
2345 | 45 | 5/2/2019 | NULL |
Desired Result:
InvoiceID_Unique | IdSponsor | InvoiceDate | IdPartner |
1 | 1234 | 1/1/2019 | 1 |
2 | 1234 | 2/15/2019 | 2 |
3 | 34567 | 11/8/2019 | 1 |
4 | 2345 | 4/15/2019 | 32 |
5 | 2345 | 6/15/2019 | 45 |
Solved! Go to Solution.
Hi @Anonymous ,
look at this.
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.
Hi @Anonymous ,
look at this.
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.
Refer, the way current employee has been created in the same way it can be doen
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |