I’m incredibly lazy, especially when it comes to things like the complexities of DAX. If I can help it, I won’t ever be making extra tables or measures unless it is something that I’ll be constantly referring to. That’s why I’m extremely glad to have learnt the Summarize function to help me in my laziness, and have applied it when going through the 25 Days of DAX challenges.
Using the sample Northwind dataset on the site, Question 22 wanted to know, using DAX, how many of the employees were above the age of 60.
While this is extremely straightforward in a real world setting, seeing as how the Northwind dataset has a whopping 9 employees, the DAX code is great at being scalable to any amount of employees, so that whatever evil corporate overlord is asking me to write this code can see who they should let go.
The Long-Winded Way
So the “easier” way to me would be to create a measure for age, which would return back a specific number for each employee. This would be accomplished as follows:
age = CALCULATE(DATEDIFF(VALUES(Employees[BirthDate]), TODAY(), YEAR))
This will return the age, expressed as the difference of the present date and the birth year of each employee, which can then be used as a measure within a new measure to bring out the total count of employees over the age of 60, which would be written like this:
Measure = CALCULATE(DISTINCTCOUNT(Employees[EmployeeID]), FILTER(Employees, [age] >60))
While this will return the desired answer of 8, it is simply far too much work for someone as lazy as myself. This sort of solution, while nice and clean, is more suitable for situations that require a constant usage of the age measure to perform a wide variety of calculations and transformations, and for the one-off use in this specific exercise was definitely not what I wanted to be doing, especially not when there were 24 more problems to be solving.
SUMMARIZE
That’s when I discovered the beautiful Summarize function. From what I can understand, this function basically creates a temporary table from which I can perform transformations and calculations with. What exactly does this mean? Well, I can create and pull from these temporary tables within the measure’s DAX without having to make all these extra measures to answer a single question!
The syntax of the Summarize function is written as SUMMARIZE (, [, ]…[, , ]…), which allows me to create a table based on specific columns in the specified table, give it a name, and then use the name of the newly summarised table, effectively working the same as creating a whole new measure.
So using this new Summarize function, the DAX would appear as such: SUMMARIZE(Employees, Employees[EmployeeID], "age", DATEDIFF(VALUES(Employees[BirthDate]), TODAY(), YEAR))
Now, we are ready to tackle the question again, but all in one single measure! To do this, the new line of code would appear as
Measure = CALCULATE(DISTINCTCOUNT(Employees[EmployeeID]), FILTER(SUMMARIZE(Employees, Employees[EmployeeID], "age", DATEDIFF(VALUES(Employees[BirthDate]), TODAY(), YEAR)), [age] > 60)
Now we are calculating the unique employee IDs, but applying a filter within which we are entering this Summarize function to create the temporary age table, and filtering it so that it only returns the results that are over 60, giving us the answer of 8.
While this is definitely not best practice, especially when it comes to client work or exercises that require a lot of constant referencing back to a specific measure, boy does it feel great not needing to write more DAX than I need to.
