Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey,
In a simplified situation I have two tables, with the following fields
Table1 CustomerID Date Price
Table2 CustomerID StartDate EndDate customertype
I want to combine the Price from Table1 with the customerType in Table2 if the customerID's in the two tables are corresponding and if the date in Table1 is between StartDate and EndDate of Table2. How can I do this in PowerBI?
In SQL I can do this in a statement like
Select Table1.Price, Table2.customerType from Table1 inner join Table2 on Table1.CustomerID = Table2.CustomerID and Table1.Date >= Table2.StartDate and Table1.Date <= Table2.Enddate
Thanks in advance for any idea.
Eddy
Hi @eddydm,
In this scenario, you should be able to use LOOKUPVALUE function to denormalize the "customertype" directly from Table2, by defining a new calculated column in Table1.
The formula below is for your reference.
customertype = CALCULATE ( LOOKUPVALUE ( Table2[customertype], Table2[CustomerID], Table1[CustomerID] ), FILTER ( Table2, Table2[StartDate] <= Table1[Date] && Table2[EndDate] >= Table1[Date] ) )
Regards
Hi @v-ljerr-msft,
Nice solution if we are sure that there is a one to one relation between the two tables.
What if in table2 there could be more records for one customer?
Kind regards
Hi @eddydm,
What if in table2 there could be more records for one customer?
Do you mean that a specific Date for one customer could belong to different date ranges for the same customer? The same customer could belong to different customer types? If so, that's a very different and strange scenario.
So what's the relationship between Table1 and Table2? Is it "1 : M" or "M : M"? Could you post some sample data in your scenario?
Regards
Hi @v-ljerr-msft,
Let's start with the data in table 2
CustomerID StartDate Enddate customerType
1 1/01/2013 31/12/2154 A
2 1/01/2012 31/12/2014 A
2 1/01/2015 31/12/2154 B
3 1/01/2013 31/12/2018 A
3 1/01/2013 31/12/2018 B
So I have a very easy customer 1 which has only one type during a whole periode.
Customer 2 has changed from Type A to B at a certain date.
Customer 3 is the 'complex' one who has two types in the same periode.
Data from table 1
CustomerID Data Price
1 25/05/2016 200
1 30/01/2017 250
2 20/03/2013 300
2 15/01/2017 275
3 30/10/204 225
The result I want to get is
CustomerID Data Price customerType
1 25/05/2016 200 A
1 30/01/2017 250 A
2 20/03/2013 300 A
2 15/01/2017 275 B
3 30/10/204 225 A
3 30/10/204 225 B
The solution with the lookupvalue should work for customerID 1 & 2, but there stays a problem for customer 3
Thanks in advance for any additional idea.
Eddy
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |