Forum Discussion
Creating a moving average for count data ... but counting up a string column
Can't you just use COUNT inside of CALCULATE?
Note that you can't use DATESBETWEEN unless you have a proper date dimension table set up, so you might have to use a different approach like this:
SMA (7 day) = VAR CurrDate = MAX ( ActivityLogs[RetrieveDate] ) VAR Days = CALCULATETABLE ( VALUES ( ActivityLogs[RetrieveDate] ), ActivityLogs[RetrieveDate] <= CurrDate, ActivityLogs[RetrieveDate] > CurrDate - 7 ) RETURN AVERAGEX ( Days, CALCULATE ( COUNT ( ActivityLogs[Activity] ) ) )
Can't you just use COUNT inside of CALCULATE?
Note that you can't use DATESBETWEEN unless you have a proper date dimension table set up, so you might have to use a different approach like this:
SMA (7 day) =
VAR CurrDate = MAX ( ActivityLogs[RetrieveDate] )
VAR Days =
CALCULATETABLE (
VALUES ( ActivityLogs[RetrieveDate] ),
ActivityLogs[RetrieveDate] <= CurrDate,
ActivityLogs[RetrieveDate] > CurrDate - 7
)
RETURN
AVERAGEX ( Days, CALCULATE ( COUNT ( ActivityLogs[Activity] ) ) )This actually works so well! Against a count of all activity, you can really see the comparison between the 7 day average and daily counts.
Unfortunately, I ran into another error: I have the Activity grouped in another column:Activities_Grouped = SWITCH( TRUE(),CONTAINSSTRING(ActivityLogs[Activity], "View") = TRUE, "View",CONTAINSSTRING(ActivityLogs[Activity], "Create") = TRUE, "Create" etc ... etc .... Around 15 of these.
So when I used this column as the legend, I get an error: "Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.. The exception was raised by the IDbCommand interface."
A web search indicated I needed to simplify the formula ... but is this even possible?
Thank you!This sounds like an issue with a calculated column being too complex for a DirectQuery, which is an entirely different question. Ideally, you could add that column at the data source rather than with DAX. I can't really think of a workaround to simplify a column definition like that.
Thank you very much for the insight! I didn't actually think of that. I'll ask my team if that column would be possible at the source.
