Featured Issues

DataPro is a weekly, expert-curated newsletter trusted by 120k+ global data professionals. Built by data practitioners, it blends first-hand industry experience with practical insights and peer-driven learning.Make sure to subscribe here so you never miss a key update in the data world.  IntroductionDAX context is the foundation of every accurate Power BI calculation. This article explains what DAX context is, why it affects every formula you write, and how row context, query context, and filter context shape the results returned in Power BI reports. The single most important concept to understand when writing DAX calculations is context. Context is what separates DAX, as a data analysis language, from Excel functions or SQL queries – or Power Query scripts, for that matter. While all of these only return different results when the data changes (with some exceptions, such as when using parameters), a single DAX formula can provide many different results depending on where and how you use it: the context.DAX context is also the key to achieving advanced results with DAX. After you have overcome typical beginner's mistakes, such as not knowing what DAX functions to use, incorrect syntax, or forgetting parentheses, issues with context are the most common problem when working with DAX. We will even go as far as to state the following:Every problem in DAX comes from context, and every solution is found by closely examining context.This statement is seldom negated!The Power BI modelThe examples in this chapter are taken from a simple Power BI model. The model consists of one fact table, fSales, with some filter tables: Figure 4.1: The sample Power BI modelNote:The model file for this chapter, Context and Filtering.pbix, can be downloaded from https://github.com/PacktPublishing/Extreme-DAX-Second-Edition/tree/main/Ch04.Introduction to DAX contextThe generic term for DAX context is evaluation context: it is the context in which a DAX formula is evaluated, leading to a specific result. We like to distinguish between three types of context:Row contextQuery contextFilter contextIn most Power BI documentation and publications, only two types of context are identified: row and filter context. The term query context has been used in relation to Power Pivot in Excel since way before Power BI came into being, and we have kept using it. In our experience from DAX courses, distinguishing between query context and filter context helps people understand more complex scenarios. Note that query context is not directly related to DAX queries, as discussed in Chapter 3, Using DAX.Let's look at each context type in more detail.Row contextRow context is, in some sense, the simplest context in use. This is the type of context you work with when creating calculated columns. Row context is context that works across a specific table, in a row-by-row fashion. A DAX formula is evaluated for each row in the table, and the row context provides information about the values of each column in the table. That means that in row context, you can directly reference a column and get a specific value in return, which you can use for a calculation.The result of the calculation is, typically, specific to each row. This is because the values that are used in the calculation can be different in each row. For instance, a calculated column in the fSales table to compute margin as the difference between sales and costs would look like this:Margin = fSales[SalesAmount] – fSales[Costs]You can immediately spot that this formula is used for a calculated column by the direct reference to the SalesAmount and Costs columns. This can only be done within a row context, and this is what makes this context type different from other types. (In simple calculated column formulas, the fSales table reference is often omitted.)Note that this direct reference only works for column values in the current row (the row for which the formula is currently evaluated): to retrieve values from other rows, you will need to take a completely different approach. This is a fundamental difference from calculations in Excel, where it is quite common to take a value from the row above this one. It is easy to understand why, when you realize that there is no strict order between the rows in a table in a Power BI model.There are only a few DAX functions specifically designed to work in a row context. If the table containing the calculated column is related to another table, in each row, you can retrieve the corresponding value from a column in the other table using the RELATED function. The formula that follows leverages a relationship between the fSales[CityID] and Cities[Country] columns to retrieve the year for each row:Country = RELATED(Cities[Country])The result is a Country column in the fSales table:Figure 4.2: Adding a Country calculated column (some columns removed for readability)There is one restriction required for RELATED to work: the relationship must have unique values in the "other" table (that is, it must be a one-to-one or many-to-one relationship to the related table) – in this example, the Cities table. After all, the formula must result in a single value.When the cardinality of the relationship is reversed, you can use the RELATEDTABLE function. For example, if you wanted to add a calculated column to the Cities table with the number of sales transactions for each city, the following formula would do the trick:Number of Transactions = COUNTROWS(RELATEDTABLE(fSales))The RELATEDTABLE function results, for each row in Cities, in the set of rows in fSales that are related to that row. As this is a table, it cannot be used directly as values in the calculated column; in this case, we use COUNTROWS to simply count the number of rows in that table.Although RELATEDTABLE is meant for use in a row context, it is fundamentally different from RELATED in that it uses a different context type under the hood.NoteAs discussed in Chapter 3, Using DAX, we discourage the use of calculated columns. This does not mean that you will not have to deal with row context. Row context plays an important role in DAX table functions as well. More on that later in this chapter.We'll move on to the other context types now. There are some peculiarities about row context that can be made clear after we have discussed query and filter context.Query contextYou work with query context when you use DAX measures. Like row context before, query context is what makes a DAX measure return a specific result. The difference, of course, is that we are not working within a single table. In short, query context refers to the set of rows in a Power BI model that are selected and over which the DAX formula is evaluated. It helps to distinguish between two separate, although closely related, elements in the query context:A selection refers to the set of rows in each table in the model that are selected in a specific context.A filter is an instruction to select one or more values in one specific column in the model. This causes rows to be selected.In a query context, the filters come from elements in a (Power BI) report. These come in various sorts: slicers, filters in the filter pane, labels in a visual, or selected items in another visual.Each of these forms a specific instruction for a selection within a column; for instance, in Figure4.3, the slicer induces a filter on the Year column: Year equals 2026. Furthermore, the value of 2.5bn is returned for the month of February, because the month label in the visual creates an additional selection on the Month column: Month equals February.There can be many filters on different columns, and there may even be multiple filters on the same column. Together, the filters determine which rows are selected in each table: all rows that satisfy every filter instruction.Note:There is a thorough discussion of visual filters in Chapter 9, Working with Auto-Exist.Figure 4.3: A simple Power BI reportIn a query context, relationships between tables play an important role: that of filter propagation. This means that a filter on a column in one table is propagated by the relationship to the other table, in the relationship's cross-filter direction:Figure 4.4: Filter propagation by a relationship happens in the direction of the arrowStrictly speaking, it is not the filter itself that is propagated, but rather the effects of the filter: in the related table, only rows that correspond to rows that satisfy the filter rule are selected. This is exactly the behavior that is needed: when a slicer is set on the year 2026, as in Figure 4.3, we want to see results for 2026, which means that all calculations should only be done on rows in the fact table that correspond to dates in 2026.Because of the nature of the query context, you cannot directly use columns in a formula as you can in a row context. The following formula, as a measure, is not accepted by the DAX editor:Report Year = 'Date'[Year]It results in an error message: A single value for column 'Year' in table 'Date' cannot be determined. The reason is that, conceptually, the selection could contain multiple values. This is true even when the column contains only one unique value, or when the table contains only one row.Filter contextFilter context looks similar to query context, with one important exception: filter context is a context changed by DAX code, for example, by adding or changing filters in a query context.The central DAX function that is used for this is CALCULATE (or its sibling, CALCULATETABLE).The way changing context with CALCULATE works is discussed in depth in the DAX filtering: using CALCULATE section later in this chapter.What makes filter context difficult is that you cannot determine the filters in the context from the visual, like you can with query context. Working with filter context requires a level of abstract thinking and meticulously keeping track of which filters are active in a specific situation. For the same reason, measures that create and use filter context can be confusing to the report users and should therefore be applied with care, by using self-explanatory measure names, for example.Being able to change context opens up a plethora of possibilities not available with merely row context and query context. It allows us to obtain results that do not correspond with the query context, and that can be used to provide advanced insights, such as comparing sales for a product with sales of all products, comparing this year's sales to last year's, extrapolating trends into the future, and so on. In fact, none of the scenarios discussed in the remaining chapters of this book are possible without a filter context.The general approach to creating sophisticated insights with DAX can be described as follows:1. Study the (possible) query contexts in which your calculation will be evaluated.2. Determine what filter context is needed to return the required result.3. Determine how to go from query context to filter context.To master DAX, you should acquaint yourself with this way of thinking, which is fundamentally different from retrieving data with SQL, programming, or doing calculations in Excel.Detecting filtersFilters in an evaluation context cause the selection of rows in the tables of a model. When you consider the effect of this on a single column, several things can happen. It could be that no selection is made, such that all values in the column are in the context. It could also be that a subset of values is selected. This can be caused by a filter on that column, in which case we say that the column is filtered directly, or it can be caused by a filter on another column in the same table or a filter in another table, which is propagated by a relationship. No matter where that filter comes from, we say that the column is indirectly filtered or cross-filtered.DAX contains a number of functions to detect the filters in the context and their effect. Each function takes a column reference, say column A, as an argument:ISFILTERED: Returns True when there is a filter directly on column A.ISCROSSFILTERED: Returns True when there is a filter on any column in the model that causes a selection in column A.HASONEFILTER: Returns True when a (direct) filter on column A selects exactly one value.HASONEVALUE: Returns True when a filter on any column in the model causes a selection of exactly one value in column A or, by coincidence, column A contains a single value to begin with.FILTERS: Returns a table with the selection in column A that is the result of the direct filters on that column.ISINSCOPE: Returns True when exactly one value is selected in column A, caused by a filter on column A, which is used as a grouping column or label in the visual. This function is designed to detect the current drill-down level in a visual that allows for drill-down.ISATLEVEL: A similar function to ISINSCOPE, but ISATLEVEL is only available in visual calculations. When you want to detect filters in visual calculations, ISATLEVEL is recommended to ensure that it works as expected in the context of the visual matrix, in which visual calculations are defined.These functions can be of help if you want to investigate what the context looks like. They can also be used to implement specific DAX measure behavior, although there are pitfalls along the way. You can find some examples in Chapter 5, Security with DAX.Comparing query and filter context to row contextNow that we have covered query and filter context, we can look at row context from a different perspective. As an example, suppose you create a calculated column in the fSales table with the following formula:TotalTax = SUM(fSales[Tax])You will find that in the resulting TotalTax column, each row contains the same value. The SUM function calculated the total of all rows in the table, even though we are in row context for a single row. To DAX beginners, this is often a surprising find. Let's look at another example – this time, a calculated column in the Date table:TotalShipping = SUM(fSales[ShippingCosts])Again, you will find that same result in every row, even though there is a relationship between the fSales and Date tables. Shouldn't the relationship cause the calculation to return the total shipping costs for each day separately?These examples teach us something about the nature of row context. The TotalShipping example suggests that in a row context, relationships do not propagate selections. This is a useful rule of thumb to work with, but the reality is a bit more subtle. In a row context, DAX specifically allows for using column values from the same table; outside of that, nothing is selected or filtered. In a calculated column, there are no filters on any column in the table. As a consequence, relationships have nothing to propagate. This means that when you refer to another table, as the TotalShipping calculation does, you work with the complete table. Even when you refer to the table in which the calculated column is, as in the TotalTax calculation, you are looking at all rows.As a consequence, if you are in a row context but need a relationship to propagate, you must find a way to change the row context to a filter context. And for that, you must use the CALCULATE function.ConclusionUnderstanding DAX context is the key to building reliable, flexible, and advanced Power BI calculations. Row context, query context, and filter context each shape how a formula is evaluated, while functions such as CALCULATE, RELATED, RELATEDTABLE, and filter-detection functions help control or inspect that behavior. The central lesson is clear: most DAX problems can be traced back to context, and the strongest DAX solutions come from carefully identifying the current context, deciding the context required, and transforming one into the other.This article is an excerpt from Extreme DAX, Second Edition, a practical guide to taking Power BI and Fabric analytics skills to the next level.Author BioMadzy Stikkelorum has a master's degree in Mathematics and works at Quanto where she enjoys helping clients solve complex problems with Microsoft Power BI solutions. She is co-author of the book Microsoft Power BI Visual Calculations: Simplifying DAX and she launched the video training Mastering Business Intelligence with DAX, Power BI, and Excel in 2025 with Michiel. Madzy lives with her husband and two sons in the Netherlands, and in her spare time she loves to read, bake, and work on creative projects of all kinds-with or without sons, with mixed results regarding creativity and complexity.Michiel Rozema is one of the world's top Power BI experts. He holds a master's degree in mathematics and has worked in the IT industry for over 30 years as a consultant and manager. Michiel was the data insight lead at Microsoft Netherlands and launched Power BI in the country. He is the author of multiple books on Power Pivot and Power BI and has published video courses on Microsoft Fabric and Power BI on LinkedIn Learning. Michiel is founder of Quanto, organizer of the Power BI Summer School, and speaker at many conferences on Power BI. He has been awarded the Microsoft MVP award since 2019.Henk Vlootman is a senior global Power Platform, Power BI, and Excel business consultant. Every year since 2013, Henk has received the Microsoft MVP award for his outstanding expertise and community leadership. Henk is one of founders of the Dutch Power BI user group and the organizer of the Power BI Summer School, and has been a speaker at many conferences on Power BI all over the world. He is also the author of two Excel and two Power Pivot / Power BI books. He started his career in 1992 with his own company, then as an Excel consultant. Nowadays he runs the consultancy company Quanto, specialists in Power BI, together with fellow MVP Michiel Rozema.

Yann LeCun’s warning, Anthropic’s expansion, and a deeper look at agent evaluation AI_Distilled #141: What’s New in AI This Week Vector memory for AI agents in air-gapped, regulated, and offline environments VectorAI DB delivers sub-15ms retrieval for agent memory and RAG pipelines on your own infrastructure. On-premises, at the edge, or air-gapped. Native support for LangChain, LlamaIndex, and Hugging Face. Free Community Edition available. Get Started for Free LLM Expert Insights, Packt LATEST DEVELOPMENTS 📉 AI pioneer warns of industry bubble as costs outpace revenues - AI researcher Yann LeCun has criticized Elon Musk’s xAI as a struggling competitor in the race for frontier AI while warning that the broader industry risks a “big bubble explosion” if leading labs fail to reduce costs or raise prices. LeCun argued that today’s AI services remain heavily subsidized by investors and suggested that more advanced AI systems may ultimately require new architectures beyond large language models. 🧠 MIT gives robots a memory that works more like ours - MIT researchers have developed a new memory framework that allows robots to remember objects, locations, and past observations using natural language, enabling them to answer questions such as “Where did I leave my wallet?” By combining 3D mapping with AI-generated descriptions, the system could help future robots navigate complex environments and collaborate more naturally with humans. 🌏 Microsoft becomes the primary gateway for OpenAI models in China - While OpenAI and Anthropic have largely stayed out of the Chinese market, Microsoft has emerged as the main supplier of OpenAI’s models to major Chinese technology companies through Azure. The arrangement highlights Microsoft’s unique position in the global AI ecosystem, even as concerns grow around model distillation, geopolitical tensions, and the flow of advanced AI capabilities across national boundaries. 🇰🇷 Anthropic expands into South Korea with new office and AI partnerships - Anthropic has opened a Seoul office and announced partnerships with major Korean organizations, including NAVER, Samsung SDS, LG CNS, and Nexon, as demand for Claude continues to grow across the region. The company also signed an agreement with South Korea’s Ministry of Science and ICT to collaborate on AI safety, cybersecurity, and responsible AI adoption. ⚖️ Study highlights why AI still struggles to moderate online hate speech - New research shows that leading AI moderation systems often disagree on what constitutes hate speech, producing inconsistent results across demographic groups and content types. While AI can detect explicit abuse at scale, researchers say it still struggles with context, sarcasm, coded language, and reclaimed terms, underscoring the challenges of relying on automated systems for online content moderation. Claude is currently the most powerful tool of 2026. Yet almost no one knows how to actually use them. Our expert mentors have condensed 800+ hours of Claude research, articles, YouTube content and real-world practice into a focused 16-hour curriculum. Join the 2-Day Claude AI Mastery Workshop: a live, end-to-end deep dive into Claude plus 10+ AI tools, LLMs and workflows. You will learn how to: - master Claude's three modes : Chat, Cowork and Code. - Set up Skills, Connectors and Plug-ins to automate your desktop, Notion and files. - Vibe code apps and dashboards without writing code & 10+ AI tools and workflows that pair with Claude. 🧠 Saturday & Sunday 🕜 10 AM – 7 PM EST Register NOW! 📈EXPERT INSIGHTS Why a Good Answer Doesn’t Mean a Good Agent During a time when AI conversations are often louder than they are useful, Ammar Mohanna, PhD, brings a refreshing perspective. His career has moved fluidly between academia and industry, from teaching advanced AI courses at the American University of Beirut to advising teams on turning machine learning ideas into systems that can be trusted. He is also known for his candid take on the current AI landscape, especially the gap between meaningful engineering and what he often calls AI slop. In this conversation, Ammar challenges one of the most common assumptions in agent development: that a correct answer is evidence of a successful agent. He explains why reliability lies in the path an agent takes, not just in the result it produces, and why evaluation must evolve from output scoring to a discipline that measures behaviour and trustworthiness in production. Most teams think they’re evaluating agents, but they’re actually not. Where do you see the biggest illusion of evaluation today? The biggest illusion is that teams think they are evaluating an agent when they are only evaluating the final answer. That works well for a chatbot. But an agent is different. It plans, chooses tools, passes arguments, reads observations, retries, stops, and sometimes takes action. A final-answer score hides most of the actual failure surface. An agent can produce a good-looking answer after calling the wrong tool, wasting ten steps, misreading a tool result, or ignoring a failed call. From the outside, the answer may look acceptable. From a reliability perspective, the run is not acceptable. So the illusion is: “the answer looked right, therefore the agent worked.” However, what you need to know is whether the path was valid, efficient, grounded, and safe. Read the Full Interview on Substack Most Claude Code content focuses on prompts and quick wins. This workshop explores what comes next. Join Sam Keen, former engineer at AWS, Lululemon, and Nike, to learn how high-performing teams use structured context, reusable skills, workflow memory, and guardrails to get more consistent results from Claude Code. 🎟️ Exclusive for AI Distilled subscribers: Get 60% off with code AI60. Limited to the first 10 sign-ups. Register Now Built something cool? Tell us. Whether it's a scrappy prototype or a production-grade agent, we want to hear how you're putting generative AI to work. Drop us your story at nimishad@packtpub.com or reply to this email, and you could get featured in an upcoming issue of AI_Distilled. 📢 If your company is interested in reaching an audience of developers and, technical professionals, and decision makers, you may want toadvertise with us. If you have any comments or feedback, just reply back to this email. Thanks for reading and have a great day! That’s a wrap for this week’s edition of AI_Distilled 🧠⚙️ We would love to know what you thought—your feedback helps us keep leveling up. 👉 Drop your rating here Thanks for reading, The AI_Distilled Team (Curated by humans. Powered by curiosity.) *{box-sizing:border-box}body{margin:0;padding:0}a[x-apple-data-detectors]{color:inherit!important;text-decoration:inherit!important}#MessageViewBody a{color:inherit;text-decoration:none}p{line-height:inherit}.desktop_hide,.desktop_hide table{mso-hide:all;display:none;max-height:0;overflow:hidden}.image_block img+div{display:none}sub,sup{font-size:75%;line-height:0} @media (max-width: 100%;display:block}.mobile_hide{min-height:0;max-height:0;max-width: 100%;display:none;overflow:hidden;font-size:0}.desktop_hide,.desktop_hide table{display:table!important;max-height:none!important}.social_block .social-table{display:inline-block!important}}