Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Should I expect slow query response times when filtering an imported table with a dynamically selected date where it falls between row effective start and end dates? The link below contains a simple example with a year-over-year variance against 660K rows. It takes 8 seconds to trend 365 days. I expect the response time to be immediate with several times as many records.
Example of DAX I'm trying to speed up:
In reality, I need to select between effective dates in tables between 13M and 20M rows, slice them by 10 different attributes, include one or two more date filters, etc.
I've also tried:
I am always required to either; (1) expand the context of the customer and date records I'm directly linked to in the relationships, or (2) work with disconnected tables. In either case, opening up context kills the query time.
When I profile the script there are huge combinations of effective dates passed into an IN clause in the xmSQL, but I don't know if that's something the vertipaq engine should be able to handle well. I'm concious of the preference to keep things in the storage engine, but the filter always increases the time used by the formula engine considerably. It does appear to reuse most of the SE cache.
Any help is much appreciated! I'm not sure how to get this reassigned to the proper DAX category.
The Italians have some great videos on how to refactor queries to reduce cardinality. Apply filters first that have the biggest impact, then apply the other filters sequentially.
Instead of FILTER() try using CALCULATETABLE() . Use DAX Studio to watch for the number of records in each query plan.
Thanks for the reply. I've been enjoying their videos already...feels like I've tried it all!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |