cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: How can I further optimize this DAX formula?


@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.

 

   

Super User
Super User

Re: How can I further optimize this DAX formula?

@markus_zhang

 

Take a look at this video by master Ferrari. Interesting stuff in it and a good way to ignite your interest in optimization. 

Re: How can I further optimize this DAX formula?

@AlB

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%.

Super User
Super User

Re: How can I further optimize this DAX formula?

@markus_zhang

 

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 

Highlighted
Super User
Super User

Re: How can I further optimize this DAX formula?

@markus_zhang

 

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 Smiley Surprised You can also see the massive difference in the percentage of time spent in the formula engine versus in the storage engine.

 

Original measure timings.jpgInitial measureModified measure timings2.jpgModified measure

 

Super User
Super User

Re: How can I further optimize this DAX formula?

@markus_zhang

 

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

COUNTROWS(EXCEPT(A, B))

 

but I think the approach above is simpler and probably faster (although probably not by a lot)

Super User
Super User

Re: How can I further optimize this DAX formula?

@markus_zhang

 

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] )

 

Re: How can I further optimize this DAX formula?

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]" )
)