Dear DAX Diary,
If you even scratch the surface of DAX literature, you’ll run into variations on the following theme: DAX is a language of tables. In DAX, a filter is a table. With DAX, you need to “think in tables.”
If you’re a database developer, it’s not such a great leap. But for a typical analyst, even one who is great in Excel, it’s hard not to squirm in your chair and think: “What does that even mean?”
That was my first thought, at least, and I have a pretty extensive database background. When I bellyached about that to Brian Grant he sent me to read Jeffry Wang’s 2011 seminal blog, The Logic behind the Magic of DAX Cross Table Filtering, which just made me want to cry.
But the great thing about someone who loves to teach is that if you complain enough about a concept, he or she will think of a way of unpacking it until it actually becomes accessible. Brian’s video “Thinking in Tables” from the Elements of Dax series is a condensed, powerful unpacking of what it means to start thinking in tables.
If you want to deeply understand DAX, you’ll have to get comfortable with everything in DAX being tables.
Brian has a useful analogy. If you’re on a desert island and the only vegetation is bamboo, you build everything out of bamboo. Your house, your ropes, your cooking ware, all bamboo-derived products. If you come from a world where ropes and cookware are made from different materials, this may seem like a limitation. But on the island, it’s all bamboo, and you get used to it.
In DAX, it’s all tables. The trick is getting used to it.
Let’s just look at one concept, which is the most counter-intuitive. In DAX, a filter is a table. My theory is that this is hard to grasp because it goes against everything we intuitively know about filtering.
Think of a little kid with blue boxes and red boxes on a table. Ask the kid to take away the red boxes. The kid identifies the boxes where the color is red and pulls them out. The kid does not think, “I’m doing a left join on a single-row, single-column table where the value of the column [Color] is red with the table of all the boxes (and all their colors) on the table.”
In fact, the kid does do a sort of Boolean thing. The kid picks up a blue box and asks: “Is it TRUE that the color is red? No, that’s FALSE, put it back.” But the kid doesn’t have to think of it as a table. The kid thinks of it as a one-at-a-time True/False question.
And just like the kid, somewhere behind all the machinery, DAX does get to a simple true/false question about each row or value. It’s probably safe to think of it that way, if it matches intuition. But if you can stretch beyond it, you start to appreciate what’s great about thinking of filters as tables.
For one thing, the beauty of filters being tables is that you can have a bunch of values on a single column that you want to filter by. If the orange box below is the filter context, the filters “Shift” and “Dish” are the tables you filter by. Shift is a single value (Dinner), Dish has multiple values (Burger, Salad).
This allows for a lot of flexibility on the front end. No matter what the end-user chooses, be it a single value or multiple values, DAX is ready for it. It’s already positioned as a table.
Another good reason to think of Filters as Tables is that the first time you run across a DAX function where a whole table is used as a filter, you won’t have a meltdown. Take this (which I don’t advise as a measure, but just to illustrate).
Customers w Sales=
CALCULATE (
COUNTROWS ('Customer'),
Sales
)What’s really going on here has to do with the concept of Expanded Tables, which is not something to cover here. But it makes my point—if you start to think of Filters as Tables when you first see a Table as a Filter you’ll be ready for it.
Expanded Tables, Filters as Tables, Temp Tables, Summary Tables. Better than bamboo, but it will take some time to get comfortable with. This video series is one to watch and re-watch, which has been my experience of DAX in general.
Don’t get discouraged, it’s a language, and languages take lots of repetition. But it’s elegant and powerful, and that makes all the effort worth it.
Thanks for listening to my thoughts today, Dax Diary. Until next time.
Yours,
Rachel
