View Transcript
Using DAX to Filter Power BI Data
Power BI users often struggle with report performance and accurate data models due to misunderstood filtering. Filters determine what data calculations see, and poor DAX usage leads to slow visuals or incorrect results. Many IT pros build reports without diving into data modeling, missing opportunities to optimize.
In this hands-on session, Jessica Jolly (Microsoft Certified Trainer) demonstrates essential DAX filtering techniques using CALCULATE and evaluation contexts. Participants practice with a pre-built Northwind data model in Power BI Desktop, copying measures from provided text files to avoid syntax errors. The focus stays on DAX mechanics, not visualizations or Power Query.
What You Will Learn
- How CALCULATE works with expressions and table-based filters to modify evaluation context
- The difference between filter context and row context, including inner and outer evaluation
- Building expanded tables in DAX and their role in data model relationships
- Context transition automatic up and why it matters for measures versus calculated columns
- Using ALL and REMOVEFILTERS modifiers to clear specific or all filters in CALCULATE
- Applying ALLSELECTED, KEEPFILTERS, and ALLEXCEPT for precise control over slicer interactions
- Creating calculated columns like Line_Item_Total = 'Orders Details'[UnitPrice] * 'Orders Details'[Quantity]
- Debugging DAX with IntelliSense and avoiding common syntax errors through copy-paste practice
Key Takeaways
1. Treat filters as tables in CALCULATE. Jessica shows how every filter argument passes a table, enabling you to inspect and modify what reaches your expression for better control.
2. Prioritize data model design before DAX. A strong model with proper relationships reduces DAX complexity, as expanded tables reveal how filters propagate across fact and dimension tables.
3. Use ALL modifiers strategically for performance. Instead of broad ALL, target specific columns with ALLEXCEPT to maintain necessary filters and speed up report pages.
4. Practice row context with iterators. Jessica demonstrates gross sales calculations using SUMX to iterate rows, transitioning context automatically for accurate totals.
About the Speaker
Jessica Jolly is a Microsoft Certified Trainer specializing in Power BI. She transitioned from facilities management at Unilever to full-time training in 2016, after volunteering on Microsoft Office at her local library. She splits time between Chicago suburbs and western Maine, balancing tech with outdoor pursuits.
Who Should Watch This
This session suits Power BI users responsible for data modeling or report performance who know basics like Power BI Desktop but need DAX skills. You will benefit most if you handle ad-hoc models or troubleshoot slow visuals, as Jessica reinforces why DAX matters through practice.
Complete beginners should first install Power BI Desktop, review CALCULATE basics, and download workshop files including Northwind data and measures text file. Advanced DAX experts may find the fundamentals familiar, but the hands-on emphasis on contexts and modifiers offers practical refreshers for teaching teams.
If you build reports without modeling and avoid custom measures, skip this; focus on visuals instead. The 45 minutes deliver dense practice value for those ready to copy measures and experiment in real-time.
Note: Before You View the Recording
This is not a workshop designed for a complete beginner in Power BI. It is hands-on.
We recommend before you begin you read all the posts below this one. They will help you:
- Know a little about the "Calculate" function in DAX. Have a read of our post below to help you
- Install Power BI desktop. Here's a link to get you to the right place if you haven't already.
- Download the workshop files (2 * excel files containing sample data, a Power BI file to use for your build)
- Take a look at these extra resources below so you are ready to use them at the relevant point in the video.
Measures File (Use this)
During the workshop, you will need these examples. You can copy and paste them into the expression bar at the correct time.
➡️ DOWNLOAD ⬅️
Create a Column Example (Use this)
In the early part of the workshop, Jessica will add a new column to the Orders Details table. This is the DAX expression to use for that step:
Line_Item_Total='Orders Details'[UnitPrice] * 'Orders Details'[Quantity]
Workshop Sample Files (Download These)
To prepare for the workshop, as it will be very hands-on, we recommend you take some time before starting to perform the following actions:
- Download and unzip the files linked here to a location you can use during the workshop
- Make sure you have downloaded and installed Power BI Desktop. (open it to be sure its working for you)
- Make sure you can open the Northwind excel 'database'. You'll use this a lot during the class
- Make sure you can open the Row context and duplicates excel file. Again, this is used during the class.
Power BI Desktop Download (Install This)
For the workshop, you will be getting very hands-on. To do this, you will need the desktop app installing....➡️Download Power BI Desktop ⬅️
There is NO MAC version of Power BI Desktop. You can create a VM to install the app but if this means you cannot take part in the workshop, please contact us using hello@collab365.com
Help With the Calculate Expression (Read This)
You'll hear that knowing a 'little' about the Calculate DAX formula is helpful to this workshop.
But Don't worry...!
If you need a refresher or some practice, Jessica will help you get used to the concept and idea in the workshop. If you are keen to know what's in store though, here's a really simple example of using Calculate to share the level you'd want to be at ready for the workshop:
UK Revenue = CALCULATE(SUM('Online Retail'[Revenue]), 'Online Retail'[Country] = "United Kingdom")
In the example above, you are creating a measure called UK Revenue (a new piece of data in your model) that is made by using the Calculate DAX expression.
Calculate is a DAX formula made of 2 parts.
The first part is the expression. In this case, in plain English "Sum up the Revenue Colum in my Online Retail table". Easy right?
Then the next part is a filter. In the example, you are saying "Sum up the Revenue in my Online Reail table...but oh! ONLY do this where the value in the Country column for each row is United Kingdom."
To round off the whole example, you are also saying "Pop me the answer into the UK Revenue measure so I can use it somewhere in my model or visual".
That's all there is to it really. Not so bad huh?
With Jessicas help, you will be building on this concept and in her words "Foolin with filters" to get you really comfortable with the DAX concepts and practices you'll want to use really often.
Note: On-demand content is taken from a live workshop. You cannot see the chat. Also in some cases like this one, there are technical challenges the presenter has to get through. Please bear with Jessica. You will learn a LOT!