Forum Discussion
matrix lowest level (rows) taking forever?
I have the below measure where I added the IF clause to avoid negative %:
GM %:=IF( NOT [Net Revenue] < 0
,DIVIDE( [Gross Margin] ,[Net Revenue])
)
Each respective measure used:
Gross Margin:=
CALCULATE(
SUM(Revenue[GrossMargin]),
FILTER('Center', NOT 'Center'[Department]in {BLANK()})
)
Net Revenue:=
CALCULATE(
SUM(Revenue[NetRevenue]),
FILTER(‘Center', NOT 'Center'[Department]in {BLANK()})
)
(the filter in these last 2 measures is used to avoid in the visuals the 'blank' group in the visuals)
Before I added the IF( NOT [Net Revenue] < 0 it worked fine, but now the report is very very slow, specifically:
I am using a Matrix, and in rows I have 4 “levels”… The report works fine on the first row/level, if I drill down, the second level takes a couple of seconds, the 3rd level takes several minutes, the 4th level times out (never displays).
In values section I have 5 different measures.
In the beginning I thought it was that the rows/Drill down was too deep… But by playing around I realize that by removing GM% I can drill down to the lowest level instantly (no wait!) !!
How can I troubleshoot this? I can fix it by either removing GM% measure or if keep it, but removing the IF it also works fine…
Is there any other way my IF would work? Or shall I do anything in the rows/drill down part??
In general, both IF() and FILTER() functions can be expensive in your model, avoid if possible.
I have applied these techniques with success.
1. Instead of using filter in the DAX measure, use a variable leveraging CALCULATETABLE(), applying the filter requirement in this variable virtual table. You can refer to this variable within your calcuate statement. While you are at it, I would split out your Sum() within its own measure, as it is most likely useful in other DAX Measures of your report. This should aid in performance as well.
Ruth from Curbal has a great video outlining how Filter() could be slow, as it acts like an iterator / x function.
https://www.youtube.com/watch?v=zMbbo013Pto2. Similiar to item 1 above, eliminate the IF() and instead apply this filter upstream within a variable calcualted table.
Check out SQLBI's DAX Guide on the IF() function. Scroll towards the bottom, youll find several articles they have written that apply to your current situation.
Every model is different with variying relationships and volumes of data, but in general these techniques has increased performance for me.
3 Replies
I have the below measure where I added the IF clause to avoid negative %:
GM %:=IF( NOT [Net Revenue] < 0 ,DIVIDE( [Gross Margin] ,[Net Revenue]) )Each respective measure used:
Gross Margin:= CALCULATE( SUM(Revenue[GrossMargin]), FILTER('Center', NOT 'Center'[Department]in {BLANK()}) )Net Revenue:= CALCULATE( SUM(Revenue[NetRevenue]), FILTER(‘Center', NOT 'Center'[Department]in {BLANK()}) )(the filter in these last 2 measures is used to avoid in the visuals the 'blank' group in the visuals)
Before I added the IF( NOT [Net Revenue] < 0 it worked fine, but now the report is very very slow, specifically:
I am using a Matrix, and in rows I have 4 “levels”… The report works fine on the first row/level, if I drill down, the second level takes a couple of seconds, the 3rd level takes several minutes, the 4th level times out (never displays).
In values section I have 5 different measures.
In the beginning I thought it was that the rows/Drill down was too deep… But by playing around I realize that by removing GM% I can drill down to the lowest level instantly (no wait!) !!
How can I troubleshoot this? I can fix it by either removing GM% measure or if keep it, but removing the IF it also works fine…
Is there any other way my IF would work? Or shall I do anything in the rows/drill down part??
In general, both IF() and FILTER() functions can be expensive in your model, avoid if possible.
I have applied these techniques with success.
1. Instead of using filter in the DAX measure, use a variable leveraging CALCULATETABLE(), applying the filter requirement in this variable virtual table. You can refer to this variable within your calcuate statement. While you are at it, I would split out your Sum() within its own measure, as it is most likely useful in other DAX Measures of your report. This should aid in performance as well.
Ruth from Curbal has a great video outlining how Filter() could be slow, as it acts like an iterator / x function.
https://www.youtube.com/watch?v=zMbbo013Pto2. Similiar to item 1 above, eliminate the IF() and instead apply this filter upstream within a variable calcualted table.
Check out SQLBI's DAX Guide on the IF() function. Scroll towards the bottom, youll find several articles they have written that apply to your current situation.
Every model is different with variying relationships and volumes of data, but in general these techniques has increased performance for me.
Anonymous Try reversing the logic to get rid of the NOT, IF([Net Revenue]>=0 ?
