It’s interesting to watch students learn Power BI and respond differently to new concepts.
ETL, modeling, and design are mouse clicks for the most part. However, it’s Evaluation Context and DAX that requires us to slow down, comprehend, conceptualize, and practice hands-on.
I tell students that DAX is what separates a drag and drop user from a true report developer. If you dig into learning these concepts, you’ll come out with a superpower! The trick is finding students that want to learn ‘how’ Power BI works, not just ‘what’ it can do.
Here are 10 tips I use to help others. I’d love to hear what helps you learn or teach DAX to others:
⚡️More visuals the better. I come from an Excel background and find that pivot tables help demonstrate evaluation context and report level aggregations in a familiar way. Knowing that similar core concepts were brought into Power BI gave me confidence early on.
⚡️Filtered models and measures work together, and that’s not easy to keep track of when trying to figure out how new DAX functions are used. I built a sample report matrix visual that includes a tool tip visual illustrating the filtered model behind the curtain. As you hoover over different matrix cells, the tool tip displays surviving rows of the model specific to the active filters in each cell. Solidifying that the current filter context (or active filters applied) is different for each cell or data point.
Click the image below to view the sample report in Power BI. Use a PC to display the tooltip filtered model visual.
⚡️Big data can blur our sense of us right and wrong. I am big fan of a small model. Stop and reduce your model to maybe two dimensions and a ten row fact table. Seeing obvious right and wrong answers has helped me gain confidence in troubleshooting DAX many times over. Another confidence builder is using real-world data that is familiar and meaningful to you.
⚡️Knowing when we have row context in the middle of our measure matters. I point out that the most heavily used iterators require a table first… SUMX, FILTER, etc. it’s important to know what an iterator function looks like, and what to expect from your expression.
⚡️ Materialize concepts that process behind the scenes. If we don’t understand the result of our SUMX result, let’s use a calculated column. What happens when we use SUM vs. CALCULATE? Does filtering that table matter?
⚡️Master sales margins. Most learning models are based on a retail environment and contain sales and cost detail. The math involved to create total sales, cost, margin dollars and percentage are well known and useful when learning the necessity of row and filter context. We need measures to divide margin percentage, and we need row context to calculate quantity and price etc.
⚡️Rob Collie’s GFITW rocks! I touch on built-in time intelligence functions, but prefer using custom time intelligence (FILTER(ALL(TIME…). It’s consistent and always works with a solid time table. As a bonus, it’s a great way to teach CALCULATE modifiers 🙂
⚡️I also use the “Colie method” star schema layout to emphasize the flow of dimensions with strong relationships into our fact table. If I’m feeling brave, I’ll also touch on how that flow creates an expanded fact table.
⚡️VARIABLES VARIABLES, and more VARIABLES! When I calculate a complex Excel expression, I use ‘helper columns’ to help separate logical steps. Variables do the same in Power BI. They are logical, more efficient, and useful when we need to store and use a result from the outer filter context.
⚡️Formulas found in the wild on YouTube or from an AI helper is no substitute for fully comprehending the DAX language. For me it’s just like wanting to be fluent in a foreign language. It would take much more than asking where the bathroom is etc. If I were to start over in my learning, I’d supplement hands on practice with Matt Allington’s book Supercharge Power BI, then move to SQLBI and read the Definitive Guide to DAX. I’d also make a habit of learning from others connect with others on YouTube like Guy in a Cube, and seek out community opportunities such as SML (Saturday Morning Learning). While I may never consider myself a DAX expert, I appreciate the online community and the continuous learning that Power BI has brought to my development.
As always, thank you for reading. If you have feedback please reach out!
Leave a Reply