BI as Business Insight

Proving that what you do has real impact.

Grouping in Ranges/Bands

Scenario

In my previous post “Ranges as Filters for Dates” I covered a way to group on ranges of months that we could use in selections. In this post I’ll share a way to simplify the measures using DAX’s SWITCH function, and also apply it to other cases.

HowTo

In the previous post we used several nested IF statements to calculate the column “Time Bucket”… let’s simplify that by using the SWITCH function.

  • The first argument is the expression to evaluate. Because we’re creating time buckets, we need conditions that will evaluate to TRUE/FALSE, and we want to take as value the first TRUE statement… so, the expression is TRUE().
  • Then we have the list of pairs of “condition to evaluate”/”value to return if true” in order of precedence, because the SWITCH function will return the value of the first condition that finds being TRUE.
  • Lastly, the value for the ELSE.

image

Similarly, we can use SWITCH to simplify the column’s formula that will return a numeric value that we’ll use to order the previous column as explained in that post.

Results

Same as in the previous post. For example:

image26

Related Posts