Performance question about looking up a value between two dates

Question

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).Model mockupThis answers the question "Who was the sales person for a given rate at the time of the quote?"&nbsp;Here is my calculated column:SalesPersonId(fx) = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX(Rate[SalesPersonId])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Rate,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Rate[BeginDate] &lt;= Quote[QuoteDate]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&amp;&amp; Rate[EndDate] &gt;= Quote[QuoteDate]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&amp;&amp; Rate[QuoteId] = Quote[QuoteId]))&nbsp;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.&nbsp;My question: is there is a better/faster/easier way to do this? The performance just seems so slow.&nbsp;Thank you!!

anonymous · Answer

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 · Answer

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!

Forum Discussion

Performance question about looking up a value between two dates

2 Replies

Related content

Dataverse query performance question

Performance question

Bookmark questions

Architecture question and brainstorming

Field Parameter Question