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.
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:
