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.
Hi
I am in the early stages of learning DAX. I have what I think is a simple chain of measures BUT my Quantity Sold (over nWeeks) is taking 3 minutes to reurn the results so clearly I have an issue somewhere. The table contains an item number (dimension) which can have upto 99,999 rows (which I know is not ideal) and contains a couple of measures. When I remove the Quantity Sold (over nWeeks) measure it returns immediately, I believe this to be the throttle point.
Total Quantity Shipped =
SUM ( 'fctSales'[Quantity Sold] )
Max Invoice Date =
MAX ( 'fctSales'[Invoice Date] )
Number of Weeks for AWS (Default: 26) =
SELECTEDVALUE('WeekRange'[pWeekRangeForAWS], 26)
nDays (# of Days) =
7 * [Number of Weeks for AWS (Default: 26)]
nWeeks Start Date =
DATEVALUE ( [Max Invoice Date] - [nDays (# of Days)] )
Quantity Sold (over nWeeks) =
VAR StartDate = [nWeeks Start Date]
RETURN
CALCULATE (
[Total Quantity Shipped],
FILTER (
'dimCalendar',
'dimCalendar'[DATE: DATE] >= StartDate
)
)
Hopefully someone will be able to help and save me. Thank you for your time and reading this.
Oli
From what I can tell, a low number of Storage Engine queries is good BUT a high percentage of time in the Formula Engine (99%) is bad. And I know that the total time is shocking.
Any further advice (other than give up 😏) would be great.
Thanks again
Hi @music43
Can you share the pbix? Optimization frequently depends on your data structure and other particularities of the model. Try this to start with
Quantity Sold (over nWeeks) =
VAR StartDate =
MAX ( 'fctSales'[Invoice Date] )
- 7 * SELECTEDVALUE ( 'WeekRange'[pWeekRangeForAWS], 26 )
RETURN
CALCULATE ( [Total Quantity Shipped], 'dimCalendar'[DATE: DATE] >= StartDate )
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@music43 , this seems fine. Do you bi-directional joins and a lot of visuals on the page.
Can make bi-directional join as single direction and check.
if not can you share the data model?
Hi @amitchandak
The model hasn't got any unusual relationships.
Unfortunately I can't share the model as it contains company data ☹️
The only other useful information I could provide is this:
and the query performance code (which I don't fully understand yet)
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"6 weeks"}, 'WeekRange'[Week Range (for AWS)])
VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('dimCalendar'[DATE: DATE])),
AND(
'dimCalendar'[DATE: DATE] >= DATE(2020, 1, 1),
'dimCalendar'[DATE: DATE] < DATE(2020, 12, 1)
)
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL('dimProduct'[PRODUCT: NUMBER TEXT], "IsGrandTotalRowTotal"),
__DS0FilterTable,
__DS0FilterTable2,
"Number_of_Weeks_for_AWS__Default__26_", 'M E A S U R E S'[Number of Weeks for AWS (Default: 26)],
"Product_Age_in_Weeks__new_", 'M E A S U R E S'[Product Age in Weeks (new)],
"Total_Quantity_Shipped", 'M E A S U R E S'[Total Quantity Shipped],
"Quantity_Sold__over_nWeeks_", 'M E A S U R E S'[Quantity Sold (over nWeeks)]
)
VAR __DS0PrimaryWindowed =
TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'dimProduct'[PRODUCT: NUMBER TEXT], 1)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC, 'dimProduct'[PRODUCT: NUMBER TEXT]
Thanks
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |