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 III
Now I'm trying to figure out how to calculate the number of "At risk members", which is defined as who signed-up 29-85 days before and never signed-up 0-28 days before. If they signed up 85+ days before and never signed up since then, they are "Dormant".
A sample diagram:
-200 day: d
-100 day: b
-98 day: c
-54 day: a
-54 day: c
-40 day: c
-13 day: a
-10 day: b
So I have a total of 4 members, a and b are Active, c is At-risk and d is Dormant.
I don't think this can be achieved as easily as the Active Member Count measure because it involves basically two passes:
Pass 1 - Grab all members who signed-up in (-85, -29]
Pass 2 - Grab all members who signed-up in (-29, 0]
Then use maybe an INTERSECT to find members who signed up in both periods, and these should be excluded.
So basically it is:
DISTINCTCOUNT of all NTKLogin[email] between (-85, -29] relative to SELECTEDVALUE(WeekMatrix[Date])
- DISTINCTCOUNT of all NTKLogin[email] of the INTERSECT
I think this is slightly better than the method I used before (very similar to the one I pasted, with ADDCOLUMNS), I'll try it out, see how fast it goes and get back to you guys.
#Edit: Can optimize the formula - If SELECTEDVALUE(WeekMatrix[Date]) is less than 85 days from the earliest date in NTKLogin, can substitute with a simple [All Members] - [Active members]
- Greg_Deckler
Why can't you just use MIN of the number of days? If you did something like:
Table = SUMMARIZE('Table',[User],"__days",MIN([Days]))
Your __days column would dictact what category they fell into.
- markus_zhang
Hi Greg_Deckler thanks, it's a bit embarassing but I'm not familiar with SUMMARIZE(), guess it's time to learn it for real!
- Greg_Deckler
It's one of my favorites.
https://docs.microsoft.com/en-us/dax/summarize-function-dax
However, my understanding is that SUMMARIZE is sort of being phased out with the replacement being SUMMARIZECOLUMNS:
https://docs.microsoft.com/en-us/dax/summarizecolumns-function-dax
If you know SQL, think GROUP BY
