Join or add column based on multiple date range and categories

[RafDelgado]

Hey All, 

I'm working with two tables that contain 44k rows of data from Dataverse and an API web call. One table has the customer information and the type of service they had during a period. The second is a table which shows all the data time entries by everyone. 

CRM Data Verse

Customer	StartDate	EndDate	ServiceType	ContractName
Customer A	12/04/2018	12/04/2019	Service Type 1	Contract 1
Customer B	12/07/2018	12/07/2019	Service Type 1	Contract 2
Customer A	13/06/2018	13/06/2022	Service Type 2	Contract 3

API

Agent	ServiceType	Customer	Creation Date	TimeSpent
Agent A	Service Type 1	Customer A	22/04/2018	150
Agent B	Service Type 2	Customer A	14/06/2020	160
Agent A	Service Type 1	Customer B	13/07/2019	5

I created a list first, which on query editor works as expected, but it's ridiculously slow to load on refresh as it's almost manually checking on each 44k row.

List.First(
Table.SelectRows(
Contract,
(r) =>
r[StartDate] < [CreatedDate]
and
r[EndDate] > [CreatedDate]
and
r[Customer] = [Customer]
and
r[ServiceType] = [ServiceType]
)[ContractName]
)

Is there a way to add a column to the second table on the data model based on multiple conditions between X date that matches multiple columns and returns a string value that's a quicker way to join? Like the below?

API

Agent	ServiceType	Customer	Creation Date	TimeSpent	ContractName
Agent A	Service Type 1	Customer A	22/04/2018	150	Contract 1
Agent B	Service Type 2	Customer A	14/06/2020	160	Contract 2
Agent A	Service Type 2	Customer B	13/07/2019	5	Contract 3
Agent B	Service Type 1	Customer A	23/07/2018	100	Contract 1

[v-rongtiep-msft · Accepted solution]

Hi @RafDelgado ,

Please have a try.

Create a measure.

Measure =
CALCULATE (
    MAX ( TableA[ContractName] ),
    FILTER (
        ALL ( TableA ),
        TableA[ServiceType] = SELECTEDVALUE ( API[ServiceType] )
            && TableA[Customer] = SELECTEDVALUE ( API[Customer] )
    )
)

Or a column.

column =
CALCULATE (
    MAX ( TableA[ContractName] ),
    FILTER (
        ( TableA ),
        TableA[ServiceType] = EARLIER ( API[ServiceType] )
            && TableA[Customer] = EARLIER ( API[Customer] )
    )
)

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.