When we think about measures in SSAS Tabular, we typically assume a numeric output. In this article, I’d like to present the case for non-numeric measures. Firstly we’ll look at what a non-numeric measure is, then identify use-cases, and finally move through a couple of ways to create them. In the final section, we will also look at methods to deal with any complexities that may arise.
How can a measure be non-numeric?
DAX is an incredibly versatile language, and the range of values that can be returned by a DAX calculation is not limited to numeric values. You can set up a measure that returns a boolean, date or even text value. As DAX grows in popularity, I wouldn’t be surprised if we will even be able to create measures that return images or media. Creating a measure with one of these data types is as simple as writing a formula which returns a non-numeric value.
Has Countries with > 50million people? =
VAR CountriesOver50Mil =
COUNTROWS ( Countries );
FILTER ( Countries; Countries[Population] > 50000000 )
The DAX measure above returns a BOOLEAN value indicating whether the current selection of countries contains 1 or more with a population of more than 50,000,000 people.
Range of Players :=
VAR MinTime =
MIN ( Times[Time] )
VAR MaxTime =
MAX ( Times[Time] )
AND ( ISBLANK ( MinTime ); ISBLANK ( MaxTime ) );
"From " & MinTime
& " to "
& " minutes."
The measure above returns a text string, listing the minimum and maximum times, in a specific table. For example, a table of doctor appointments, or marathon completion times. This is more intuitive than having multiple columns in certain scenarios.
For a far more thorough explanation of the underlying theory and more examples, I write about Non-Numeric measures in great depth on SQL Server Central.
These concepts also appear in my talks, “Best of Class: A DAX Measure for Top Performers”, and my “Working with Complicated Data Models in DAX” session.