Level: Beginners/Intermediate
As those that read my blog regularly would know, I have been at the Microsoft Data Insights Summit in Seattle Washington for the last 2 days. I had the privilege and pleasure of presenting two sessions at the event. For those that were not able to attend, here are the videos of my sessions.
DAX 50
I was honoured to be asked by Will Thompson to present this as a joint session. The whole idea was to start from the very basics and make it easy for anyone to understand what DAX is all about. If you are new to DAX, or just want a refresher, then This is the video for you.
Evaluation Contexts in DAX
This next session was designed for those that already know some DAX but haven’t quite conquered context transition yet. And the good news is you can watch it a few times if need be.
I love hearing from people about what they think and what doesn’t make sense. Let me know what you think in the comments below.
Incredible, I loved its content. I hope to see more functions and formulas here.
Thanks Matt, your presentations were excelent!. You have a special way to teach.
Awesome Matt – I enjoyed both of the Videos
Hi Matt, thanks for this video.
How do you explain this : I have a calendar table linked to a fact table, when i insert a quarter slicer, i get all the quarters of the calendar table, I only need four quarters, whatever the year selected in the pivot table ?
What do you mean “whatever the year selected in the pivot table?” Do you have a pivot table filter on year from your calendar table? Assuming the quarter comes from the calendar table too, I assume it will all work as you describe
PS nice presentation Matt. 20 minutes more would have been perfect.
@Jeff sometimes Calculated Columns are unavoidable. If you need a calculation to loop through a subset of products (think EARLIER function) then it becomes difficult to write a Measure for that. Matt has a great Blog using Calculated Columns to do “banding” but uses Look-Up Table to minimise memory hit. But sometimes it just won’t work on the Look-up and you need to use Fact table. If performance slows you can always look to Italians for alternate solution.
I liked it a lot, but I still struggled in places. Here’s some thoughts on things that I had to think really hard about. (Note that your presentation helped me immensely, in that it had me thinking really hard about the stuff I still wasn’t quite following, which then helped crystallize the learning).
• In regards to your early coverage of Calc Columns vs Measures, it might be good to give a few examples of when you would use a calculated column e.g. when you need the result to appear in a Slicer. (You do mention some considerations in response to the 2nd and 3rd questions you receive)
• Love the way you show how the formula SUM(Sales[SalesAmount] + Sales[TaxAmt]) throws an error because of lack of context, but SUMX(Sales, Sales[SalesAmount] + Sales[TaxAmt]) passes, because you have explicitly given it the context in which it is working (thanks to that Table parameter).
• Might be worth pointing out that while SUM(Sales[SalesAmount]) + SUM( Sales[TaxAmt]) works AND gives you the correct answer because row context simply doesn’t matter due to the associative property of addition i.e. because we are simply adding a whole bunch of stuff up, it doesn’t matter what order we add the things up in. But a similar example using multiplication – such as SUM(Sales) * SUM(TaxRATE) wouldn’t give the correct answer (I presume), because you NEED a row context to get the right result given that multiplication AND addition is involved, and the order matters.
• You get a 2nd question about 31:30 into the presentation but you forgot to repeat it for the benefit of the video audience. Maybe you could add this in as a callout in the video?
• I love how you take the Sum(Sales[Sales Amount]) that returns the same number for each row, and then show what happens when you wrap a CALCULATE around it. But I’ve always struggled to comprehend the sentence that CALCULATE “converts a ROW context into a FILTER context”. My brain just goes blank when trying to interpret that, and I think it’s because I’m still thinking of FIlTER as meaning “You know, the effect that Rows, Columns, and Slicers have on a measure back in the UI”. But in writing this comment, I’ve realsed that that ‘other’ thing I understand as being FIlTER context back in the UI isn’t what you’re talking about at all, but rather you mean this: CALCULATE allows you to add a virtual filter to a calculated column, using the columns of that table as coordinates, almost as if that Table was sitting on the canvas, providing it’s coordinates to some calculation. Is this correct?
• I also don’t follow what Marco means when he says that the ROW context disappears and we’re just left with the FILTER context.
• Your Context Transition In Action slides are awesome.
• Really handy to learn that measures have an Implicit Calculate
Overall a fantastic video, and I can’t wait to see your PowerBI and DAX courses.
“CALCULATE allows you to add a virtual filter to a calculated column, using the columns of that table as coordinates, almost as if that Table was sitting on the canvas, providing it’s coordinates to some calculation. Is this correct?”
Using the ROWS of the table as coordinates. It converts the row context (which row am I on) into a filter context.
Thanks for pointing out the locked view that filters come from visuals. I will try to call out the difference in future.
It’s still a convoluted topic but, I liked the examples used in the Evaluation context presentation.
Thanks for that Matt! I love your teaching style. Cheers from Brazil.