Who needs numbers?

Reading Time: 2 minutes

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 )
    > 0

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] )
    IF (
        AND ( ISBLANK ( MinTime ); ISBLANK ( MaxTime ) );
        BLANK ();
        "From " & MinTime
            & " to "
            & MaxTime
            & " 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.

SQL Server Central Article

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.


Leave a Reply

Your email address will not be published. Required fields are marked *