Got a DAX question recently that I solved using variables. When I walked them through the solution someone exclaimed “I didn’t know you can do this with variables!”. Variables are a great way to divide tough problems into small logical increments. Today’s blog post is not about the actual DAX being used but more to show how useful variables can be.
Now the scenario we have is one around event analytics. We have a table of ID’s representing a product, a sequence (can also be a date) and the event type:
Now we want to add a slicer to the report that allows us to slice information by every event group. A event group contains all events in sequence started off by Type 1. So it the event group resets every Type = 1, each individual events is ordered by sequence. So I decided to add a calculated column to do this. There are probably also good ways of doing this in M or different DAX techniques but this one worked for me.
EventGroup =
//Get the current sequence, we are in row context so we can get the value for the current row
VAR curSequence = Temp[Sequence] //Get the current ID, we are in row context so we can get the value for the current row
VAR curID = Temp[ID] //Get a table of all the events for the current Id
VAR tblCurEvents =
FILTER ( ‘Temp’, Temp[ID] = curID ) //Now look for all the events before the current sequence
//Instead of using the variable I could also have used EARLIER(Temp[ID])
VAR tblCurPrevEvents =
FILTER ( tblCurEvents, [Sequence] <= curSequence ) //Finally get a list of all the Type 1 events
VAR TblEventStart =
FILTER ( tblCurPrevEvents, [Type] = 1 ) //Finally count the amount of events left
RETURN
COUNTROWS ( TblEventStart )
Again you can see how easily I was able to split up the problem into subsequent steps and potentially debug each step by returning intermediate results. Now I could have also written it like this:
EventGroup =
VAR curSequence = Temp[Sequence]
VAR curID = Temp[ID]
VAR curEvents1 =
FILTER ( ‘Temp’, Temp[ID] = curID && [Sequence] <= curSequence && [Type] = 1 )
RETURN
COUNTROWS ( curEvents1 )
but that is not as easy to read and debug, performance wise there is no difference in this case.
I could have even written it without using any variables, it is nice and compact even less readable:
EventGroup 3 =
COUNTROWS (
FILTER (
‘Temp’,
Temp[ID] = EARLIER ( Temp[ID] )
&& [Sequence] <= EARLIER ( Temp[Sequence] )
&& [Type] = 1
)
)
The result is a new column that groups the events together:
That’s it, hope you find this tip useful. You can download the workbook here.
I can’t find RETURN in the DAX Function reference (https://www.google.nl/search?q=dax+%2B+return+-returns+site:https://msdn.microsoft.com/en-us/library/&ei=R5XAWrkkh8_BAv_UuOgC&start=0&sa=N), I guess you learned about this in your previous job at the DAX team?
The RETURN function is part of the VAR function https://msdn.microsoft.com/en-us/library/mt243785.aspx and cannot be used without it.