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

Performance question about looking up a value between two dates

I have two tables: Quote and Rate related by QuoteId (see image). A quote can have multiple rates but only one can exist at any given point in time as determined by Begin and EndDates. The sales person is associated to the rate. I've written a calculated column that correctly pulls the sales person from the rates at the time of the quote (QuoteDate).

postimage.pngModel mockup

This answers the question "Who was the sales person for a given rate at the time of the quote?"

 

Here is my calculated column:

SalesPersonId(fx) = CALCULATE(
                                                    MAX(Rate[SalesPersonId])
                                                   ,FILTER(
                                                               Rate,
                                                               Rate[BeginDate] <= Quote[QuoteDate]
                                                                 && Rate[EndDate] >= Quote[QuoteDate]
                                                                 && Rate[QuoteId] = Quote[QuoteId]))

 

Again, this works, but it seems very slow. It takes about 4-5 minutes of spinning on my desktop. The dataset is only about 120k rows.

 

My question: is there is a better/faster/easier way to do this? The performance just seems so slow.

 

Thank you!!

2 REPLIES 2
Super User
Super User

Re: Performance question about looking up a value between two dates

Think the issue here is that you are using Filter on a Fact table, which is generally bad for performance. Any chance you can upload some sample data?

hyggins Frequent Visitor
Frequent Visitor

Re: Performance question about looking up a value between two dates

Actually, neither of these are fact tables. No measures are defined. The Rate table is only needed to define the SalesPersonId on the Quote table. The Quote table is then used as a dimenision for a larger dataset (not shown). This is a simplified example for the post.

 

And I have the Quote and Rate table data available here: sample data file

 

Thank you!

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 278 members 2,912 guests
Please welcome our newest community members: