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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
eddydm
Advocate II
Advocate II

Relations between tables with supplementary filter on non-key fields

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

 

 

4 REPLIES 4
v-ljerr-msft
Employee
Employee

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?Smiley Happy

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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