Forum Discussion
Advocate IIIHow can I further optimize this DAX formula?
Hi markus_zhang
Unless I'm missing something, what your code does in a slightly convoluted manner is count how many users (emails) have logged in on the date selected in the slicer or any of the previous [ActiveThreshold] days. If so, you can simplify the code a whole lot:
Measure =
IF (
ISFILTERED ( WeekMatrix[Date] ),
VAR _SelectedDate = SELECTEDVALUE ( WeekMatrix[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( NTKLogin[email] ),
NTKLogin[logins] <= _SelectedDate, NTKLogin[logins] >= _SelectedDate - [ActiveThreshold]
),
ERROR ( "This measure can only be evaluated under the filter context of WeekMatrix[Date]" )
)
Community ChampionHi markus_zhang
Unless I'm missing something, what your code does in a slightly convoluted manner is count how many users (emails) have logged in on the date selected in the slicer or any of the previous [ActiveThreshold] days. If so, you can simplify the code a whole lot:
Measure =
IF (
ISFILTERED ( WeekMatrix[Date] ),
VAR _SelectedDate = SELECTEDVALUE ( WeekMatrix[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( NTKLogin[email] ),
NTKLogin[logins] <= _SelectedDate, NTKLogin[logins] >= _SelectedDate - [ActiveThreshold]
),
ERROR ( "This measure can only be evaluated under the filter context of WeekMatrix[Date]" )
)
Advocate IIIAlB Thanks! There is a small modification I made to your code and then it works perfectly! It doesn't speed up a lot but I think it reduces the memory print.
Measure =
IF (
ISFILTERED ( WeekMatrix[Date] ),
VAR _SelectedDate = SELECTEDVALUE ( WeekMatrix[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( NTKLogin[email] ),
FILTER(
ALL(NTKLogin),
AND(NTKLogin[logins] <= _SelectedDate,
NTKLogin[logins] >= _SelectedDate - [ActiveThreshold])
)
),
ERROR ( "This measure can only be evaluated under the filter context of WeekMatrix[Date]" )
)
TBH now I look back and have no idea how and why I came up with that monstrous query...
- AlB
markus_zhang wrote:AlB Thanks! There is a small modification I made to your code and then it works perfectly! It doesn't speed up a lot but I think it reduces the memory print.
markus_zhang True, the simplified syntax for the CALCULATE filter arguments does not allow invoking measures.
Are you sure it doesn't speed it up considerably? I find that very hard to believe. Have you checked with a large enough table to be able to appreciate the difference or are you running the comparison on a small fragment of the original table?
I don't think the memory print would change at all, since we just modified the code in a measure.
- AlB
Take a look at this video by master Ferrari. Interesting stuff in it and a good way to ignite your interest in optimization.
- markus_zhang
Weird, it does speed up considerably today when I'm working over VPN, actually it is almost instant. Makes sense though as it should speed up. Thanks!
For the memory print, I think I didn't clarify, it's the memory Power BI uses when it's evaluating the measures. For the original ones (I have three similar measures for Active, At Risk and Dormant members) evaluating all of them will usually gives me a out of memory error, but the new ones only pushes memory usage to about 48%.
- AlB
Ah ok. I see what you mean. Yeah the memory used should be different as your measure I believe would use the Formula Engine heavily and therefore it needs to take up larger chunks of memory when it is executing