@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.
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%.
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
You piqued my curiosity so I ran a quick test with a table with about half a million rows. Smaller than yours but well, I didn't want to wait much. I measured the performance with DAX Studio.
The results are telling; see the screen caps below. The modified measure is more than 50 times faster You can also see the massive difference in the percentage of time spent in the formula engine versus in the storage engine.
Perhaps a new thread should be opened for this but anyway. The number of active, dormant and at risk users could be calculated as follows, taking into account that the three segments are mutually exclusive.
Dormant users are all those who have not logged in during the last 85 days:
DormantUsers = VAR _DormantOffset = 85 //Change this if necessary VAR _DormantCutOffDate = SELECTEDVALUE ( WeekMatrix[Date] ) - _DormantOffset VAR _AllUsers = DISTINCTCOUNT ( NTKLogin[email] ) VAR _UsersAfterDormantCutOff = CALCULATE ( DISTINCTCOUNT ( NTKLogin[email] ), NTKLogin[logins] >= _DormantCutOffDate ) RETURN _AllUsers - _UsersAfterDormantCutOff
It could also be done as you suggested with set functions
TableA = all emails
TableB = all emails with login in the last 85 days
but I think the approach above is simpler and probably faster (although probably not by a lot)
Then, active users are those who have logged in over the last 28 days:
ActiveUsers = VAR _ActiveOffset = 28 //Change this if necessary VAR _ActiveCutOffDate = SELECTEDVALUE ( WeekMatrix[Date] ) - _ActiveOffset RETURN CALCULATE ( DISTINCTCOUNT ( NTKLogin[email] ), NTKLogin[logins] >= _ActiveCutOffDate )
and finally, at risk users are all the rest so we can use the previous two measures:
AtRiskUsers = VAR _AllUsers = DISTINCTCOUNT ( NTKLogin[email] ) RETURN _AllUsers - ( [ActiveUsers] + [DormantUsers] )
Thanks @AlB I already got those measures, I think we are using the same method but your code is more readable, I'm going to create some VARs too.
Really appreciate you and @Greg_Deckler, can't do without you guys' help.
Here is my version (I use a slightly different def of At Risk, Inactive and Dormant in my code but the gist is the same)
I also have to use a filter inside CALCULATE to grab ALL(NTKLogin) and filter it further.
In Risk Member Count = VAR _SelectedDate = SELECTEDVALUE (WeekMatrix[Date]) RETURN IF ( ISFILTERED ( WeekMatrix[Date] ), CALCULATE ( DISTINCTCOUNT ( NTKLogin[email] ), FILTER( ALL(NTKLogin), AND( NTKLogin[logins] <= _SelectedDate, NTKLogin[logins] > _SelectedDate - [_AtRiskThreshold] ) ) ) - [Active Member Count], ERROR ( "This measure can only be evaluated under the filter context of WeekMatrix[Date]" ) )
Inactive Member Count = VAR _SelectedDate = SELECTEDVALUE (WeekMatrix[Date]) RETURN IF ( ISFILTERED ( WeekMatrix[Date] ), CALCULATE ( DISTINCTCOUNT ( NTKLogin[email] ), FILTER( ALL(NTKLogin), AND( NTKLogin[logins] <= _SelectedDate, NTKLogin[logins] > _SelectedDate - [_InactiveThreshold] ) ) ) - [Active Member Count] - [In Risk Member Count], ERROR ( "This measure can only be evaluated under the filter context of WeekMatrix[Date]" ) )