Using variables to split up your calculations

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 )