The Superheroes of DAX, and derived measures

“Did someone say DAX?”

I wanted to start today’s post with a quick thank-you to the guys who tirelessly answer my DAX questions.

Marius Dumitru, Howie Dickerman, and Karen Aleksanyan.  You guys rock.  Thanks again for your patience educating me.  I’ll do my best to pay it back by sharing knowledge here.

(There’s no resemblance in real life to these three pictured above.  Well, except perhaps for Mr. Furious.  But it’s just an awesome movie and had three superheroes, which is rare – superheroes tend to be packaged in even-numbered groups.)

Today’s Tip:  Deriving measures from others

Today’s tip is a short lesson but boy is it helpful. 

Let’s say you have a fairly sophisticated measure like the “Number of unique products sold at number of stores” measure that I demonstrated the other day using SUMX().  And now you want a new version of that measure that only counts a specific type of product, as opposed to all products.

Copy/Paste the original measure formula?

Do you copy/paste the formula for the original measure into a new measure to make your edits?  I recommend against it, for two reasons:

1. You are now nesting an additional level of logic inside an already sophisticated formula – this makes things less readable and harder to debug (especially since v1 PowerPivot does not let you “trace into” a formula step by step.
2. Maintenance-wise this is a hassle going forward.  If you ever change the base measure, you need to change all copies of it as well.  Tedious.  And it’s even worse if you forget to change one of the copies.

Better answer:  use FILTER() against the original measure

You can avoid both of those problems with the following syntax:

    [New Measure] = [Original Measure] (<Table Expression>)

The simplest example of a table expression is to use the FILTER() function.  Let’s say we want our original measure, but only to count transaction of product type = fruit:

    [New Measure] = [Original Measure] (
                      FILTER(DataTable[ProductType]=”Fruit”)
                    )

Try it out!

Trust me, it works 🙂  And you will love it 🙂

Other flavors

I’m pretty sure that any expression which returns a table is valid as the <Table Expression>. 

EX:  I used CALCULATETABLE and it also works great.  CALCULATETABLE is like FILTER but allows for any number of filter params, whereas FILTER only gives you one.

    [New Measure] = [Original Measure] (
                     CALCULATETABLE(
                      DataTable[ProductType]=”Fruit”,
                      DataTable[Weekend]=”True”
                      )
                     )

Have fun everybody.  Catch you next week.