Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi There,
I have the following measure that is really slow to calculate, It seems like it is the filter part of the measure that is the problem.
DSC 0-2 = Var MyData = SUMMARIZE('FACT',PRODUCTS[MATERIAL DESCRIPTION],STORES[STORE NAME],"_DSC",'MEASURES DSC'[AVERAGE DSC]) Return DIVIDE( COUNTROWS( FILTER(MyData,[_DSC] >=0 && [_DSC] <3)), COUNTROWS(MyData), 0)
The Average DSC measure is as follows:
AVERAGE DSC = DIVIDE([CURRENT SOH],[AVERAGE DAILY UNITS (30DAYS)],0) CURRENT SOH = Var TheLastDate = LASTDATE('FACT'[Date]) Return CALCULATE([TOTAL SOH],'FACT'[Date] = TheLastDate) AVERAGE DAILY UNITS (30DAYS) = Var Days = 30 Var Total = CALCULATE([TOTAL UNITS],DATESBETWEEN('CALENDAR'[Date],LASTDATE('CALENDAR'[Date])-(Days+1),LASTDATE('CALENDAR'[Date]))) Var DateRange = CALCULATE(DISTINCTCOUNT('CALENDAR'[Date]),DATESBETWEEN('CALENDAR'[Date],LASTDATE('CALENDAR'[Date])-(Days+1),LASTDATE('CALENDAR'[Date]))) Return DIVIDE(Total,DateRange,0)
I have a standard dataset layout.
FACT, PRODUCT, STORES, CALENDAR. (all 1 to many)
I have also tried to do this without summarise but I get the same performance problems.
The fact table is 60M rows.
The measures is going to to be used in a card as well as a product table, store table and a combination of Product + store table.
Solved! Go to Solution.
Hi @Anonymous
So I finally settled on the below, previously it took 2:10 seconds to calculate on DAX-Studio, the below takes 1.6 seconds.
I am pretty sure there is still a better way to do this, but 1.6 seconds uncached is workable.
DEFINE VAR MyData = ADDCOLUMNS ( SUMMARIZE ( 'FACT', PRODUCTS[Index], STORES[Index] ), "S", [AVERAGE DSC] ) EVALUATE ROW ( "Result", DIVIDE ( COUNTROWS ( FILTER ( MyData, [S] >= 0 && [S] < 3 ) ), [DSC ROW COUNT BASE], 0 ) )
Any performance different if you write it like this?
DSC 0-2 = Var MyData = SUMMARIZE('FACT',PRODUCTS[MATERIAL DESCRIPTION],STORES[STORE NAME],"_DSC",'MEASURES DSC'[AVERAGE DSC]) Var numerator = CALCULATE(COUNTROWS(MyData), FILTER(MyData, [_DSC] >= 0, [_DSC] < 3)) Return DIVIDE( numerator, COUNTROWS(MyData), )
Hi @MarkCBB
Can you post some sample data. And of the measures you had indicated which is the slowest.
You can download PowerBIHelper to analyse the queries and then optimise.
http://radacad.com/power-bi-helper
Cheers
CheenuSing
@Anonymous, nope, it is just as slow and it brings back an incorrect result which is strange because everything looks correct (I fixed the FILTER to have && for both arguments.
Good spotting on the missing &&. I wrote that pretty fast. How much time is saved if you simply run this instead:
DSC 0-2 = Var MyData = SUMMARIZE('FACT',PRODUCTS[MATERIAL DESCRIPTION],STORES[STORE NAME],"_DSC",'MEASURES DSC'[AVERAGE DSC]) Return COUNTROWS(MyData)
Naturally we want to get a base line for whether the filter is the correct place to investigate. @CheenuSing has made a great sugestion. Dax Studio is another avenue also.
@Anonymous, That measure is near instant.
I am also doing testing in DAX Studio 🙂
Let us know how you go with it. You have me very curious.
I think the summarize function is partially to blame for your issues. Here is something i found that might give you another idea:
https://insightsquest.com/2016/11/05/dax-query-tuning-example/
@Anonymous, let me give that a read and try and implement a similar approach.
@Anonymous,
Quick update, while trying a few other approaches trying to narrow down the cost in performance, I tried this:
2_DSC 0-2 = Var MyData = SUMMARIZE('FACT',PRODUCTS[MATERIAL DESCRIPTION],STORES[STORE NAME],"_DSC",'MEASURES DSC'[AVERAGE DSC]) Return DIVIDE( COUNTROWS( FILTER(MyData,[AVERAGE DSC] >=0 && [AVERAGE DSC] <3)), COUNTROWS(MyData), 0)
The Above uses the Average DSC measure in the FILTER instead of the column that was created in the SUMMARIZE, and the performance is ~70% faster.
Still going to mess around a bit more with a few other ideas as well that the link you shared, updates to follow.
Hi @Anonymous
So I finally settled on the below, previously it took 2:10 seconds to calculate on DAX-Studio, the below takes 1.6 seconds.
I am pretty sure there is still a better way to do this, but 1.6 seconds uncached is workable.
DEFINE VAR MyData = ADDCOLUMNS ( SUMMARIZE ( 'FACT', PRODUCTS[Index], STORES[Index] ), "S", [AVERAGE DSC] ) EVALUATE ROW ( "Result", DIVIDE ( COUNTROWS ( FILTER ( MyData, [S] >= 0 && [S] < 3 ) ), [DSC ROW COUNT BASE], 0 ) )
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |