DAX (Data Analysis Expressions) is the programming language of Power BI and it was created in PowerPivot for Excel back in 2010, integrated in SSAS Tabular and then, in 2015 in Power BI.
There are two main types of users who are learning Power BI (and also DAX if they really want to master Power BI) :
- the Excel Users , mainly business users who were using Pivot Tables mainly to gain knowledge from their data and there are also the
- IT users that are coming form an IT background where there were other specialized tools to gain insights, and now they want to learn Power BI.
There are different challenges for both kind of users and one of them is to understand the concepts behind DAX. Without understanding them it’s (almost) impossible to really leverage DAX in Data Models
For a beginner it’s really hard at first to get his head around concepts like Virtual Tables, Row Context, Filter Context, iterators, etc but there are some very good resources on the web where all these are explained and broken down in small chunks so that everyone who really wants to understand how DAX works can learn and leverage it to master it’s data and get additional insights than regular Pivot Tables.
When it comes to DAX it all resumes basically to create expressions that gets evaluated against Virtual Tables filtered by specific Filter Context.
Every DAX formula will return a different result based on the Filter Context it is evaluated against and knowing exactly what is the Filter Context can help in understanding whether the result is the one expected or not.
Starting from Marco’s article here (https://www.sqlbi.com/articles/displaying-filter-context-in-power-bi-tooltips/) on how to display the Filter Context as a tooltip in Power BI this article will show you how to visualize the same thing with a Measure in Power Pivot for Excel.
Marco added the DumpFilters Measure as a tooltip and one could see the Filter Context like in the picture below
Ever since I first saw the article on www.sqlbi.com I was thinking to replicate this in Power Pivot and following all the steps indicated but “adapted” for Excel:
I opened DAX Studio, Connected to the PowerPivot Model and created the Measure as indicated, by Right Clicking any Table name and create Define Filter Dump Measure (All tables)
As the Measure length is variable depending on the model I had to copy it’s definition and Paste it in Excel as a new Measure …
… it didn’t work in DAX for Excel as the Excel DAX’s version is different (older) than DAX in Power BI. The result was just an error like the one below and I said to myself “well, that’s it. I’ll use it only in Power BI”
Then, after a while I was trying to explain to someone what Filter Context is in a Power Pivot file and had to Import it to Power BI to show it explicitly … but it bothered me I couldn’t do it in Excel and tried to find a solution for UNICHAR () not being supported in DAX for Excel.
Couldn’t find a solution neither in the Community and nor on the web and I tried to replace all the UNICHAR ( ) codes inside the measure with a space and a pipe “ | ”.
I copied the measure to Notepad and replaced UNICHAR(13) & UNICHAR(10) with “ “ & “ | ”
I copied the measure from Notepad to Excel as a new Measure … and IT WORKED … at least I was thinking it’s working 
as there were no errors detected
… but it was added for ALL the rows in the Data Model, regardless whether a specific date had Sales or not. Wrapping the entire measure in an IF () statement checking for Sales Amount greater than 0 solved only one problem, but clicking around I saw that the field is growing and growing.
I needed to WRAP the column but it didn’t work correctly as there was no UNICHAR ( 10 ) equivalent in Excel … so problem PARTIALLY solved
After getting this result I was still not happy as I couldn’t see each element of the filter on a separate line as I still couldn’t find a solution to replace UNICHAR (10) … but then, after attending a London Excel Meetup where Chandeep Chhabra was demoing techniques to Debug DAX (again in Power BI using CONCATENATEX ( ) ) I remembered the issue with Filter Context and kept thinking about it …
Last year I attended one of the (if not THE ) best SelfService BI trainings I ever took: SSBI Boot Camp by Ken Puls from https://www.skillwave.training . Besides the very well-structured and full of meaning content I was able to ask a lot of questions in the Live Q&A sessions to cement my knowledge … and, even if the training was over since Dec 2020, I have received an email from Ken the other day letting me know that an hour of additional content was included in the training and maybe I should take a look.
One of the videos added was demoing Filter Context in DAX for Power Pivot , explaining what’s the Filter Context for each cell … and I remembered again of my initial problem and decided to check again for a solution … and FINALLY FOUND IT:
The “trick” to get each of the element of the filter context on a separate line is to concatenate at the end of each line containing initially the UNICHAR () code another character between quotes and just use CTRL + SHIFT . This can be done using again Notepad or another Text Editor and pasting the final DAX formula inside the Excel measure field. Should you’re trying to FORMAT the measure using www.daxformatter.com you’ll get an error , therefore you should try to format DAX just before modifying it in Notepad.
I think this trick will help you both for debugging your DAX and understand what is the Filter Context and this could also be an easy way of visualizing the Filter Context for everyone who’s starting the DAX Journey.
I hope this post is useful and could potentially help you in future DAX exploring / teaching / learning.
Stay safe and never stop learning!
Cristian Angyal
