🤖Prompt Engineering: Tuning (Just the Quotes)

"Different algorithms may perform better on different types of text data and will have different vector sizes. The choice of algorithm can have a significant impact on the quality of the resulting embeddings. Additionally, open-source alternatives may require more customization and finetuning than closed-source products, but they also provide greater flexibility and control over the embedding process." (Sinan Ozdemir, "Quick Start Guide to Large Language Models: Strategies and Best Practices for Using ChatGPT and Other LLMs", 2024)

"Fine-tuning involves training the LLM on a smaller, task-specific dataset to adjust its parameters for the specific task at hand. This allows the LLM to leverage its pre-trained knowledge of the language to improve its accuracy for the specific task. Fine-tuning has been shown to drastically improve performance on domain-specific and task-specific tasks and lets LLMs adapt quickly to a wide variety of NLP applications." (Sinan Ozdemir, "Quick Start Guide to Large Language Models: Strategies and Best Practices for Using ChatGPT and Other LLMs", 2024)

"LLMs are advanced natural language processing (NLP) deep learning models. They specialize in both processing contextual language at scale and predicting the likelihood of a sequence of tokens in a specific language. The smallest units of semantic meaning, tokens can be words or sub-words and act as the key inputs for an LLM. LLMs can be categorized as autoregressive, autoencoding, or a combination of both. Their defining feature is their substantial size, which enables them to execute complex language tasks like text generation and classification, with high precision and potentially minimal fine-tuning." (Sinan Ozdemir, "Quick Start Guide to Large Language Models: Strategies and Best Practices for Using ChatGPT and Other LLMs", 2024)  

"LLMs encode information directly into their parameters via pre-training and fine-tuning, but keeping them up to date with new information is tricky. We either have to further fine-tune the model on new data or run the pre-training steps again from scratch." (Sinan Ozdemir, "Quick Start Guide to Large Language Models: Strategies and Best Practices for Using ChatGPT and Other LLMs", 2024) 

"Transfer learning is a technique used in machine learning to leverage the knowledge gained from one task to improve performance on another related task. Transfer learning for LLMs involves taking an LLM that has been pre-trained on one corpus of text data and then fine-tuning it for a specific 'downstream' task, such as text classification or text generation, by updating the model’s parameters with task-specific data. The idea behind transfer learning is that the pre-trained model has already learned a lot of information about the language and relationships between words, and this information can be used as a starting point to improve performance on a new task. Transfer learning allows LLMs to be fine-tuned for specific tasks with much smaller amounts of task-specific data than would be required if the model were trained from scratch. This greatly reduces the amount of time and resources needed to train LLMs." (Sinan Ozdemir, "Quick Start Guide to Large Language Models: Strategies and Best Practices for Using ChatGPT and Other LLMs", 2024)  

"The pre-training step is by far the biggest in terms of data and compute requirements for the whole of the LLM’s lifecycle. Yet fine-tuning is quite resource-intensive when we compare it to traditional machine learning and deep learning workflows. Fine-tuning is also a very important step in improving the quality of the models; hence, it makes sense to understand how we can optimize this step without impacting the performance. Efficiencies in this step also enable us to iterate faster, thereby improving adaptability in many fast-moving domains. In this section, we will focus on some interesting efficient method." (Joseph Babcock & Raghav Bali, "Generative AI with Python and PyTorch" 2nd. Ed., 2025)

 "There are three techniques for model domain adaptation: prompt engineering, RAG, and fine-tuning. Strictly speaking, RAG is a form of dynamic prompt engineering where developers use a retrieval system to add content to an existing prompt, but RAG systems are used so often that it’s worth discussing them separately. One critical difference with fine-tuning is that you must have access to the model’s weights, information that is usually not available with cloud-based, proprietary LLMs." (Abi Aryan, "LLMOps: Managing Large Language Models in Production", 2025)

"Before releasing the fine-tuned model to a production environment, you need to evaluate its performance and make sure it hasn’t picked up any counterproductive constructs, like biased language. Also, whenever a new version of your foundational model is released, you must fine-tune the model again and redo the training. Finally, you need to track the lineage of the training and validation data used to train the fine-tuned model. These are additional tasks that require careful execution, and using in-context learning is far simpler since it only requires you to add a handful of examples." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"Content Optimization is a pattern that uses preference tuning to produce optimally performing content. Preference tuning is a way of training an LLM on a dataset of paired items so that it outputs responses that are closer to the chosen items than the rejected ones. Unlike optimization methods, in which optimization attempts to approximate an independent source of truth, this pattern attempts to hack the model to create content the evaluation method will consider great." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"Fine-tuning LLMs can lead to catastrophic forgetting, in which the model overemphasizes the examples provided during fine-tuning and loses its previously acquired knowledge. This wipes out LLMs’ primary advantage: their comprehensive world knowledge. You can mitigate this issue by fine-tuning your model on a small dataset, for only a few epochs, and by selecting an appropriate learning rate during the fine-tuning process. Generally, you should begin the fine-tuning learning rate at the value where the pretraining phase concluded (typically around 1e-5)." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"The type of post-training you can do is intimately connected to the structure of the dataset. If the dataset consists purely of text completions, then you can use it to do unsupervised training, such as teaching a model new vocabulary or completely new associations via CPT. If the dataset consists of ideal responses to inputs (input-output pairs), then you can use it for SFT or instruction tuning. If the dataset consists of two responses to each input and notes which of the two is preferable, then you can use it for preference tuning. You can do any of these forms of post-training in a parameter efficient way or on quantized models." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

🖍️Valliappa Lakshmanan - Collected Quotes

"A RAG system needs to identify relevant text chunks from the knowledge base. It does that by building a datastore of chunks and organizing the chunks in a way that makes searching for chunks that are relevant to a query efficient and fast. This preparatory step of building an efficient data store is called indexing, and the runtime step of searching the datastore for relevant chunks is called retrieval. [...] Whenever a query is received, a question-answering pipeline is invoked This consists of two steps: retrieval and generation. In the retrieval step, the retriever finds chunks in the document store that are the most relevant to the query. In the generation step, these relevant chunks are added to the context of the prompt and used along with the user query to generate the answer. In this way, the generated answer is grounded in the relevant information from trusted knowledge sources. The response consists of the generated text as well as the relevant chunks that were used to generate the answer." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"As an AI engineer, you can ask a foundational model to directly generate the content your application needs by sending the model an appropriate text input, which is known as a prompt. However, you will face certain common problems - the generated content may not match the style you want, may be missing enterprise knowledge that the model doesn’t know about, or may lack certain capabilities." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"Authentication and authorization are important parts of any communication between clients and servers. We need to have a clear separation between different roles and to be able to specify what capabilities a particular client is allowed to invoke. MCP, however, is more of a language interoperability protocol - it doesn’t enforce any security principles. [...] Collaboration In MCP, communication between the client and the server is mostly one way, whereas in the real world, you may want collaborative, interactive workflows." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"Before releasing the fine-tuned model to a production environment, you need to evaluate its performance and make sure it hasn’t picked up any counterproductive constructs, like biased language. Also, whenever a new version of your foundational model is released, you must fine-tune the model again and redo the training. Finally, you need to track the lineage of the training and validation data used to train the fine-tuned model. These are additional tasks that require careful execution, and using in-context learning is far simpler since it only requires you to add a handful of examples." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"Content Optimization is a pattern that uses preference tuning to produce optimally performing content. Preference tuning is a way of training an LLM on a dataset of paired items so that it outputs responses that are closer to the chosen items than the rejected ones. Unlike optimization methods, in which optimization attempts to approximate an independent source of truth, this pattern attempts to hack the model to create content the evaluation method will consider great." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"Embeddings are mathematical representations of text, images, or other input types. They capture semantic meaning by mapping words, phrases, or documents to vectors in a high-dimensional space. LLMs have become exceptionally proficient at creating semantically representative embeddings because this capability forms the foundation for LLM generation. [...] While the latest LLMs provide massive context windows, encoding entire documents in a single embedding vector is often not advisable. You’ll lose the nuances of the different parts of a text, lose critical information, and hinder the RAG system from retrieving relevant information. Plus, it gets very expensive to use large context windows at scale. It’s preferable to chunk documents into information-dense and self-consistent chunks of text." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"Fine-tuning LLMs can lead to catastrophic forgetting, in which the model overemphasizes the examples provided during fine-tuning and loses its previously acquired knowledge. This wipes out LLMs’ primary advantage: their comprehensive world knowledge. You can mitigate this issue by fine-tuning your model on a small dataset, for only a few epochs, and by selecting an appropriate learning rate during the fine-tuning process. Generally, you should begin the fine-tuning learning rate at the value where the pretraining phase concluded (typically around 1e-5)." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"Foundational models are closed systems that are limited by their training data. In many cases, you’ll need to give a foundational model additional information. For example, the information may be based on recent events that had not occurred when the foundational model was being trained, or the information may have been private, confidential, or otherwise unavailable to the foundational model trainers. It’s impractical to retrain an LLM with additional knowledge or even perform continuing pretraining (CPT) on a foundational model to add knowledge to it. The cost of even a single training run is significant, and information changes so fast that CPT would have to be done very frequently." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"Foundational models can manipulate symbols and words effectively, but this may be because they are generalizing from the manipulation they have encountered in the training data, not because they grasp semantic meanings and the logical relationships between them in the way humans do. While you can use foundational models to perform many tasks, that’s because those tasks are similar to the tasks that the foundational model was trained to do." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"GenAI is so powerful and easy to use that even nontechnical users can easily prototype very compelling applications on top of GenAI. However, taking such GenAI prototypes to production is hard because GenAI models are unreliable - they can hallucinate, return different answers to the same input, and can have surprising limitations because of how they are trained." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"In 'traditional' ML, where models are predominantly trained for single problems like classifications or regressions, models require extensive training to adapt their weights or parameters to a new task. But you can use LLMs for a wide variety of tasks by changing just the prompt without changing the weights. Foundational models’ capability to adapt to new tasks based solely on examples or instructions they’re given in a prompt is called in-context learning. It emerges from training on diverse datasets that include various task formats and instructions. When given examples of a task within a prompt, the model identifies the underlying pattern and applies it to new instances. This process doesn’t modify the model’s weights, but rather, it leverages the model’s existing knowledge to perform new tasks." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"Multiagent architectures offer better scalability than monolithic approaches do. Rather than scaling vertically by increasing model size, multiagent systems can scale horizontally by adding more specialized agents as needed. You can update or replace individual agents without having to retrain the entire system, and that allows for more agile development and deployment. Computational resources can also be allocated dynamically based on current needs, with more resources directed to the most critical or computationally intensive subtasks. Multiagent systems are also more robust. Critical capabilities can be replicated across multiple agents to reduce the impact of individual agent failures. Different agents can approach problems with different methodologies, which increases the likelihood of finding effective solutions. Agents can verify each other’s outputs to identify and correct errors that might go undetected in a single-agent system." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"Prompt engineering may meet your accuracy requirements, but the prompts end up being extremely long as you encounter more and more corner cases. It’s not unusual for such prompts to amount to two or three pages of detailed instructions. The complex system instructions required to achieve that level of accuracy incur a large token count, and that makes the application too expensive to scale. [...] As your prompts become more detailed, maintenance overhead increases because the overall system is extremely sensitive to the specific wording of prompts. You’ll need to do integration testing every time the foundational model changes, and often even when one of your tools changes." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"The Chain of Thought pattern uses chain-of-thought (CoT) reasoning, which is a prompting technique that enhances LLMs’ problem-solving capabilities by encouraging them to break down complex problems into intermediate reasoning steps before arriving at a final answer. [...] CoT involves modifying the prompting strategy - instead of asking for a direct answer, the prompt explicitly requests or demonstrates a step-by-step reasoning process. There are three variants: Zero-shot CoT, Few-shot CoT, and Auto-CoT. [...] In Zero-shot CoT, you request a step-by-step reasoning by simply adding the phrase think step-by-step to the prompt. [...] In cases where the model’s pretrained capabilities do not include the specific logic needed, you can try Few-shot CoT, which involves providing the model with examples of step-by-step reasoning for similar problems before asking it to solve a new problem. In Few-shot CoT, you demonstrate the desired reasoning pattern through examples. [...] In the Auto-CoT variant, you add demonstrative examples to a wide variety of questions to a database called an example store. Most people use a document database (in which the examples are indexed by keywords in the question text) or a vector database (in which the examples are indexed by the embedding of the questions) for the example store." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"The Deep Search pattern involves an iterative loop that consists of retrieval and thinking until either a good enough answer is found or the time/cost budget is exhausted. It goes beyond traditional RAG systems in three ways. First, it adds a thinking step between retrieval and generation to determine what additional information, if any, needs to be gathered. Second, instead of a single retrieval step, Deep Search involves iterating through multiple stages of retrieval and generation. Finally, instead of just relying on adding knowledge from an enterprise knowledge base, Deep Search employs tools such as search engines and enterprise APIs to gather the knowledge it needs proactively." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)  

"The idea behind a GraphRAG is that, having discovered a chunk that has a partial answer to your question, you can retrieve related chunks. This can help you round out the context when each chunk is small. You can also index a document with a small chunk size and then store the embeddings in a tree form so that the embedding of a parent node captures the holistic meaning of all the nodes below it. You can even repeat nodes within the RAG if they make sense in different contexts. You can also index the nodes on both keywords and on embeddings to permit different types of retrieval for different types of queries. In addition, you can incorporate query-focused summarization into a GraphRAG by pregenerating and storing summaries for all groups of closely related entities." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"The Reflection pattern is an agentic approach in which an AI system evaluates its own reasoning, decisions, and outputs before finalizing them. This self-monitoring process allows the system to identify errors, improve solutions, and refine its approach to problems. Here, the AI system as a whole is reflecting and improving, and the foundational model used to generate the response remains the same. You’ll sometimes see this process referred to as self-reflection, but there’s no reason why the evaluation has to be done by an LLM at all, let alone by the same LLM that generates the content."  (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"The type of post-training you can do is intimately connected to the structure of the dataset. If the dataset consists purely of text completions, then you can use it to do unsupervised training, such as teaching a model new vocabulary or completely new associations via CPT. If the dataset consists of ideal responses to inputs (input-output pairs), then you can use it for SFT or instruction tuning. If the dataset consists of two responses to each input and notes which of the two is preferable, then you can use it for preference tuning. You can do any of these forms of post-training in a parameter efficient way or on quantized models." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"Traditional RAG systems 'guide' LLMs in generating responses by providing document chunks that are related to the user’s initial query. Incorporating external knowledge from a document store helps to reduce hallucinations, but its effectiveness relies heavily on the relevance of the retrieved document chunks. This can be problematic if the retrieval step produces irrelevant document chunks or incorrect information. The key goal of corrective retrieval-augmented generation (CRAG) is to detect and correct hallucinations before the LLM attempts response generation. It adds an evaluator to the process that evaluates the quality of the retrieved chunks for a given query before passing on the documents to the response generation step. The evaluator returns a confidence score that triggers different knowledge retrieval actions." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"What is an agent? In computer science, the term agent has long been used to describe software entities that act on behalf of users or other programs. When you invoke a foundational model, you specify a role, provide some context, and ask it to carry out some instruction. In the computer science sense, then, the LLM is acting as your agent." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

"When a RAG system generates a response, the generation can blur the boundaries of the original chunks or multiple chunks can contribute to a single statement. Some LLMs tend to paraphrase chunks in ways that will alter the direct connection to the source chunk, and the paraphrasing can make direct text matching difficult." (Valliappa Lakshmanan & Hannes Hapke, "Generative AI Design Patterns: Solutions to Common Challenges When Building GenAI Agents and Applications", 2026)

⚡️🗒️Power BI: CALCULATE [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 31-Oct-2026

[DAX] CALCULATE

• {def} function that evaluates an expression in a modified filter context [4]
• filter arguments are used as the context for evaluating the expression CALCULATE belongs to
• filter arguments for CALCULATE replace all existing filters over the same columns [6]
• the new context effected by the filter argument for CALCULATE affects only existing filters on columns mentioned as part of the filter argument [6]
• filters on columns other than those mentioned in the arguments of CALCULATE remain in effect and unaltered [6]
• creates a filter context that includes a filter for every column of the iterated table
• when a DAX formula is executed, multiple levels of filter contexts are combined [2] 
• requires an active row context [2]
• either CALCULATE is executed or a measure is invoked [2]
• implies a context transition in these conditions [2]
• the row context is transformed into an equivalent filter context [2]
•  most of the time, the context transition happens because a measure is being invoked rather than because of an explicit CALCULATE [2]
• it allows developers to
• add, keep, remove or replace filters
• activate relationships
• perform context transition
• {concept} outer filter context
• the filter context that is active when CALCULATE is invoked[2
• {concept} inner filter context
• the new filter context created by CALCULATE and active only during the execution of its first argument [2]
• leverages the context transition leads to better code [2]
• propagates through relationships, moving from one table to another [2]
• {type} implicit CALCULATE [2]
• functions can conveniently hide CALCULATE or CALCULATETABLE functions from users but still invokes them [2]
• e.g. TOTALYTD
• performs a context transition by transforming any existing row contexts into an equivalent filter context
• the measure referenced is calculated within a filter context created by context transition [2]
• happens immediately before the measure starts being executed, as if CALCULATE automatically surrounds the measure [2]
• {type} explicit CALCULATE
• actively invokes context transition [2]
• {warning} a random CALCULATE added to the code forces the context transition [2]
• may lead to unexpected results  
• {recommendation} don't add CALCULATE unnecessarily [2]
• context transition can lead to situations in which the calculation works but all control over why it works is lost [2]
• {benefit} leverage metrics without changing the underlying data by modifying the filter context 
• allows to create alternative views of the same data model without creating new tables or columns
• measures become flexible and reusable 
• because calculations are performed dynamically at query time
• {benefit} add/remove filters to calculate measures independent of user selections
• KPIs/indicators can remain stable regardless of report slicers
• {benefit} simplify complex row-to-aggregation calculations enable context transition
• allows aggregation logic to operate correctly inside iterators and calculated columns
• reduces complex manual filtering logic
• {benefit} use relationships more effectively by activating inactive relationships
• a single model can support multiple business perspectives without duplicating tables
• {benefit} create dynamic business metrics 
• metrics respond intelligently to slicers, filters, drill-downs, and report interactions.
• {benefit} improve code readability
• measures become easier to understand, maintain, and troubleshoot
• using variables and explicit filter modifications makes business logic more transparent to future developers and analysts
• {benefit} simplify complex DAX
• functions like CALCULATE, variables, and context manipulation reduce nested expressions and repeated calculations
• this generally leads to shorter, more maintainable formulas. 
• {benefit} enable the creation of complex measures that can adapt to changes in filters
• allows one measure to support many analytical scenarios without requiring multiple versions
• measures automatically recalculate based on report context, allowing
• {syntax} CALCULATE(<expression>, <filter1>, <filter2>,...)
• {parameter} expression to compute
• only at the end does CALCULATE compute its first argument after creating the new filter context [2]
• {parameter} filters
• starting from the second argument, are defined the filters used to create/modify the filter context
• the filters are applied before the first argument can be computed
• therefore, they must be evaluated first [2]
• starts evaluating the 'filter' parameters in the order in which they were provided
• accepts any number of arguments as filters. They are all intersected, meaning that all the filters work together at once [2]
• when CALCULATE places a filter on a column that already had filters set by previous functions (or by the visual), the filter placed replaces the previous filter [2] 
• ⇐ behavior can be changed by using KEEPFILTERS
• the filter can be one of three types
• condition
• one or more values are specified for a set of columns [2]
• the conditions are tables
• it evaluates this table and restricts the data context to only the rows that match the rows present in that filter table [2]
• modifier
• instructs CALCULATE to perform an action (akabehave in a certain way) 
• via REMOVEFILTERS or USERELATIONSHIP
• table
• filter is applied to the columns present in the table with an IN condition
• placing a filter on one table automatically propagates its effect to any table with a relationship with the original table, following the cross-filter direction of the relationship [2]
• {warning} filter context can change unexpectedly
• CALCULATE modifies the existing filter context before evaluating an expression 
• {warning} context transition can produce surprising results
• when CALCULATE is used in a row context (calculated columns, iterators such as SUMX()), it automatically performs context transition
• {warning} overriding existing filters
• 
  
• {warning} performance degradation
• complex CALCULATE() measures may repeatedly recalculate filter contexts
• {warning} ambiguous logic from multiple filters
• the filter arguments can interact in ways that are not intuitive

References:

[1] Greg Deckler & Brett Powell (2024) "Microsoft Power BI Cookbook: Convert raw data into business insights with updated techniques, use cases, and best practices" 3rd Ed.

[2] Alberto Ferrari & Marco Russo (2025) The Definitive Guide to DAX: Mastering the semantic model expression language for Microsoft Power BI, Fabric and Excel

[3] Jeroen ter Heerdt et al (2026) "Microsoft Power BI Visual Calculations: Simplifying DAX"

[4] Microsoft Learn (2026) DAX: CALCULATE [link] 

[5] Cooper Dunn (2025) Microsoft DP-600 Exam Practice Tests: 500 Questions for Implementing Analytics Solutions Using Microsoft Fabric

[6] https://learn.microsoft.com/en-us/dax/keepfilters-function-dax

 

⚡️🗒️Power BI: DAX Calculations [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 28-Oct-2026

[feature] DAX Calculations

• {concept} calculated table 
• {def} table created by users using DAX [3]
• stored in the data model [3]
• materialized when the model is refreshed [3]
• becomes a regular table that can participate in relationships, filtering, and reporting [3]
•  unlike a visual calculation or a measure [3]
• enables adding new tables based on data loaded into the model [3]
• instead of querying and loading values into new table’s columns from a data source [3] 
• via a DAX formula that defines the table’s values [3]
• {characteristic} locked after loading
• after the table is loaded or refreshed, its results are locked in and cannot be changed until the table is refreshed [3]
• the results are pre-computed and aren’t dynamically determined [3]
• relatively easy to understand [3]
• because the DAX statement can be inspected and the results can be predicted [3]
• the same statement always returns the same result for the same parameters until the table is refreshed [3]
• {concept} calculated column
• {def} a column created using a DAX formula
• behaves like any other column in a table [3]
• it can be used to define relationships [3]
• {characteristic} calculated row by row 
• can be based on 
• simple formulas (e.g. mathematical operations like addition)
• complex formulas (e.g. combinations of functions) 
• {characteristic} stored in the data model
• ⇒ increases the size of the model [4]
• {characteristic} calculated during refresh 
• calculated when the table is first loaded or refreshed [3]
• can be used in rows, columns, filters, or values of a matrix or any other report [3]
• {exception}[DirectQuery|composite models] computed during the execution of a query [2]
• they do not use RAM, but they are also potentially slower [2]
• they are not evaluated and stored ahead of any query [2] 
• {characteristic}static
• its results cannot be changed until the table is refreshed again [3]
• {characteristic} can be resource-intensive
• especially for large datasets [4] 
• {recommendation} should be used judiciously [4]
• {characteristic} can be used in slicers and filters
•  help create categories users can interact with
• {characteristic) can be used for sorting 
• improves report usability
• {characteristic} support hierarchies and grouping
•  helps organize data into business-friendly groups.
• context transition applies as well [3]
• where a row context is present, and whenever a function is used that iterates over multiple values (aka iterators) [3]
• e.g. SUMX 
• always executed in a row context [2]
• it's possible to create a row context programmatically [2]
• {scenario} placing calculated results in a slicer [2]
• {poor practice} define calculated columns to compute formulas on a row-by-row basis and then aggregate the values [2]
• moving the computation time at data refresh can benefit the overall user experience [2]
• many such scenarios can be successfully solved using iterators instead of creating calculated columns [2]
• categorize text or numbers, especially for filter criteria [2]
• {concept} measure
• {def} a DAX calculation that is evaluated at query time based on the current filter context and returns a result based on the current filter context
• works on sets of rows, producing aggregates of values [2]
• requires aggregators [2]
• computed over multiple lines at once [2]
• does not compute its value on a row-by-row basis [2]
• work on aggregated data [2] 
• reusable
• can reference other measures 
• {characteristic} calculated only when needed
• its output is determined not only by its definition but also by the filter context in which it's executed [3]
•  the same definition can have a different meaning based on the filter context [3]
•  the same measure returns different results depending on
• slicers
• filters 
• visual selections
• row/column groups 
• {characteristic} depends on filter context
•  evaluated within that filter context and often summarizes multiple rows [3]
• {characteristic} consumes little storage/memory
•  only the formula definition, not the results 
• {characteristic} typically used in visuals
•  it can be used by other measures, visual-level/page-level/report-level filters
• used to filter what is displayed
•  it can be used for 
• conditional formatting
• dynamic titles
• dynamic tooltips 
• dynamic format strings
• calculations groups 
• visual calculations 
• {characteristic} must be defined in a table
• a dummy table can be defined for managing (all) measures
• can be moved in between tables
• ⇐ a table is merely a container where the measure is organized without affecting its behavior [2]
• {characteristic} cant be used in relationships
• {characteristic} can't be used direct in slicer fields 
• {type} explicit measure
• created explicitly via the UI 
• can be created programmatically
• via Tabular Editor, TMDL, TOM, XMLA Endpoint, Fabric/PBI, CI/CD
•  
• {type} implicit measure
• created (automatically) when a column is added in the values area of a visual [2]
• not always available
• they do not work when calculation groups are present in the model or when they are disabled in the Tabular model [2]
• {downside} do not guarantee full control over the calculation.
• {benefit} smaller semantic models and lower memory usage
•  store only the DAX formula and metadata, not a value for every row
• {benefit} one measures supports multiple scenarios
• automatically respond to slicers, filters, page/report filters, user selection 
• {benefit} best performance
• faster refreshes, smaller model size, better availability 
• {benefit} reusability 
•  less code duplication, easier maintenance, consistent business logic
• {benefit} support advanced analytics solutions
• time intelligence, running totals, ranking, comparisons, etc. 
• {benefit} centralize business logic
• SSoT/consistent logic across reports 
• {benefit} support modern Power BI features
• tooltips, conditional formatting, dynamic titles, visual calculations, calculations groups, KPIs, etc.
• {recommendation} always define an explicit measure instead of using implicit measures [2]
• allows to modify the global behavior of the calculation without breaking existing reports [2]
• {recommendation} consider creating measures instead of calculated models
• measures are preferred whenever a calculation can be express both ways [2]
• {recommendation} limit the use of calculated columns to the few cases where they are strictly needed [2]
• increase model size, increase refresh time, consume RAM 
• {recommendation} consider moving the calculation to the data preparation stage [2]
• data should be transformed as far upstream as possible and as far downstream as necessary [2]
• {recommendation} use DAX Studio early
•  it allows to inspect the query plan, view filter context, analyze storage engine vs formula engine activity, understand why one measure is faster than another, etc. [2]
• {recommendation} break complex measures into layers [2]
• allows to validate each step independently
• 
  

References:

[1] Greg Deckler & Brett Powell (2024) "Microsoft Power BI Cookbook: Convert raw data into business insights with updated techniques, use cases, and best practices" 3rd Ed.

[2] Alberto Ferrari & Marco Russo (2025) The Definitive Guide to DAX: Mastering the semantic model expression language for Microsoft Power BI, Fabric and Excel

[3] Jeroen ter Heerdt et al (2026) "Microsoft Power BI Visual Calculations: Simplifying DAX"
[4] Cooper Dunn (2025) Microsoft DP-600 Exam Practice Tests: 500 Questions for Implementing Analytics Solutions Using Microsoft Fabric

Acronyms:
CI/CD - Continuous Integration/Continuous Development  
DAX - Data Analysis eXpressions
KPI - Key Performance Indicator
RAM - Random Access Memory
SSoT - Single Source of Truth
TMDL - Tabular Model Definition Language
TOM - Tabular Object Model
XMLA - XML for Analysis

 

⚡️🗒️Power BI: Context [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 28-Oct-2026

[DAX] Context

• {def} the set of data that a calculation, visual, or DAX expression evaluates at a given moment
• determines which rows are considered when a calculation is evaluated
• the same measure can return different results depending on the context
• enables users to perform dynamic analysis [5]
• used by measures, calculated columns/tables, iterator functions, slicers & filters, relationships, time intelligence, RLS, etc.
• can make it difficult to troubleshoot formulas [5]
• {concept} evaluation context
• {def} the set of conditions and rules that determines which data is used when a DAX formula calculates a result
• both the row context and the filter context contribute to the evaluation context [3]
• the two are very different, serving different purposes, having different features and different behaviors [3]
• {type} filter context
• {def} the set of filters that are active when a calculation is evaluated
• determines which rows of data are visible to a measure at any given moment [2]
• added when a filter constraint is applied on the set of values allowed in a column or table, by using arguments to a formula [5]
• applies or removes filters to/from tables in the semantic model [2]
•  filters tables in a model [2]
•  propagates filters through relationships [2]
•  managed by CALCULATE [2]
• generated not only by the the current visual but also by other visuals [3]
• each visual has its filter context (aka visual filter context) 
• determined by all the other visuals in the report that interact with that current visual and all the report filters applied through the filters pane (visual-, page-, and report-level filters) [2]
• each cell has its filter context (aka cell filter context) 
• the coordinates of the cell itself determine the filter context of a cell, plus the visual filter context [2]
• sources
• visuals (charts, tables, cards, etc.)
• slicers
• report/page filters
• cross-filtering from other visuals
• DAX functions
• e.g. CALCULATE(), CALCULATETABLE()
• {type} row context 
• {def} the context in DAX where an expression is evaluated for a single row at a time, giving DAX access to the values of the current row
• aka 'the current row' 
• used when Power BI is evaluating one row at a time [1]
• needed to obtain the value of a column for a certain row [2]
•  {warning} trying to access the value of a column in a measure without an active row context results in an error [2]
• exists whenever we create a calculated column or whenever we are computing an expression inside an iteration [2]
• calculated columns have an automatic row context [2]
• the row context for a formula includes the values from all columns in the current row [
• automatically follows the relationships between tables to determine which rows in related tables are associated with the current row [5]
• functions that iterate calculations over a table can have multiple current rows and current row contexts [5]
•  can be created formulas that recurse over an inner and outer loop [5]
• certain functions store the row context from the operation that preceded the current operation [5]
• e.g. EARLIER 
• the function stores two sets of context in memory [5]
• one set of context represents the current row for the inner loop of the formula [5]
• another set of context represents the current row for the outer loop of the formula [5]
• measures don't have a row context [2] 
• if is needed to evaluate an expression row by row inside a measure, is needed to start an iteration to create a row context [2]
• when using iterators, the developer is responsible for creating and handling the row context [2]
• iterates over a table and evaluate its column values [2]
• ⇐ one row at a time
• row context is the result of iterating over rows
• row context equals iteration [2] is not exactly a correct statement
• means that DAX evaluates expressions one row at a time
• during the iteration, DAX creates a row context that gives the formula access to the values of the current row
• functions like SUMX and calculated columns rely on this behavior
• the two aren't identical 
• iteration refers to the process of moving through rows
•  row context is knowledge of the current row during that process
• DAX creates a row context for the current row
• functions like a cursor [2]
• during an iteration, it points to a specific row of the table being iterated, telling the DAX formula which row is currently to be considered [2]
• it does not filter [2][3]
• it simply indicates to DAX which row is currently responsible for providing the values to perform the calculation and where the result of the calculation should go [3]
• it's irrelevant whether a column in the table is used in a particular calculation [3]
• all columns of the table are part of the row context when a calculation is performed [3]
• encountered when (aka created via)
• calculated columns
• the formula defines a calculated column [2]
• iterators 
• perform active iteration over a table [2]
• e.g. SUMX, FILTER, ADDCOLUMNS, etc.
• scans the table row by row, and during the iteration, the row context points to the current row [2]
• the row context is needed to obtain the value of a column for a certain row [2]
• iterator functions create row context while moving through rows
• help create multiple nested row contexts [2] 
• increases the complexity of the code [2]
• can transform into filter context through a mechanism called context transition [3]
• defines the value of a column by providing the exact row [2]
• calculated columns are always executed in a row context [2]
• is not the case for measures [2]
• {limitation} doesn't propagate filters through relationships from one table to another [2]
• {recommendation} use RELATED and RELATEDTABLE to direct and move the row context from one table over to another [2]
• allows to leverage the existing model relationships and the VertiPaq storage engine, avoiding expensive manual lookups and row-by-row filtering operations
• {limitation} does not place any filter [2]
• {concept} context transition 
• occurs when a row context is converted into a filter context [1]
• takes any active row context and transforms it into a filter in the filter context [3]
• requires an active row context [2]
• either CALCULATE is executed or a measure is invoked [2]
• implies a context transition in these conditions [2]
• the row context is transformed into an equivalent filter context [2]
• usually the context transition happens because a measure is being invoked rather than because of an explicit CALCULATE [2]
•  leveraging the context transition leads to better code [2]
• without context transition DAX can only see the current row
• with context transition, DAX can use values from the current row to filter entire tables and compute aggregations
• the process is automatically execute by CALCULATE and CALCULATETABLE [2]
• any statement invoking either of these two functions performs a context transition [2]
• this happens regardless of whether [2]
• the functions functions were written explicitly [2]
• CALCULATE or CALCULATETABLE is used explictely [2]
• a function that conveniently hides CALCULATE or CALCULATETABLE functions from users but still invokes them [2]
• e.g. TOTALYTD
• this happens in calculated columns, whenever [2]
• a row context is present
• a function that iterates over multiple values is used (aka iterators)
• SUMX
• [measures] whenever a measure is invoked, the context transition happens immediately before the measure starts being executed, as if CALCULATE automatically surrounds the measure [2]
• {benefit} opens new ways to optimize the code [2]
• eliminates complex lookup logic
• reduces repeated filtering
• leverages relationships
• enables reusable measures
• improves performance with the storage engine
• pushes work toward the VertiPaq storage engine
• ⇐ highly optimized for aggregations
• {concept} visual calculation [>>]
• {def} DAX calculation that runs directly on a specific visual (table, matrix, chart, etc.) [3]
• {concept} visual calculation context
• context used by visual calculations, where calculations are performed after the visual has already been built
• uses the rows and columns that are visible in the visual itself rather than directly querying the underlying model
• allows users to calculate running totals, moving averages, differences, rankings, and other analyses directly within a visual 
• {recommendation} begin/experiment with simple formulas and relationships to see how context works [5]
• fastest ways to understand how evaluation context actually works
• helps to isolate context behavior 
•  many challenges are related to relationships
• {recommendation} learn filter context before row context
•  filter context appears in almost every advanced DAX solution later 
• {recommendation} predict before you run
• discoveries/knowledge results especially when the result differs from expectations
• {recommendation} use simple/small examples
• facilitates review and troubleshooting
• {recommendation} build a playground for learning 
• an experimentation model can prove valuable over time
• {recommendation} use DAX Studio early
•  it allows to inspect query plan, view filter context, analyze storage engine vs formula 
•  

References:

[1] Greg Deckler & Brett Powell (2024) "Microsoft Power BI Cookbook: Convert raw data into business insights with updated techniques, use cases, and best practices" 3rd Ed.

[2] Alberto Ferrari & Marco Russo (2025) The Definitive Guide to DAX: Mastering the semantic model expression language for Microsoft Power BI, Fabric and Excel

[3] Jeroen ter Heerdt et al (2026) "Microsoft Power BI Visual Calculations: Simplifying DAX"

[4] Microsoft Learn (2026) Modify DAX filter context in semantic models [link]

[5] Microsoft Support (202?) Context in DAX Formulas [link]

 

Resources:  

[R1] Microsoft Fabric Community Row vs Filter Context: a Visual Guide [link] 

 

Acronyms:
DAX - Data Analysis eXpressions

RLS - Row Level Security

 

🏭🗒️Microsoft Fabric: Direct Lake [Notes] 🆕

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 13-Oct-2026

[MF] Direct Lake

• {def} Power BI semantic model table storage mode option available in MF [1]
• optimized for large volumes of data to be quickly loaded into memory from Delta tables available in the OneLake [1] 
• leverages VertiPaq query engine [1]
• {benefit} deliver query performance comparable to Import mode 
• without the management overhead of data refresh cycles to load the entire data volume [1]
• similar to Import mode
• {difference}refreshes copies only metadata (aka framing)
•  [Import mode] replicates the data and creates an entire cached copy of the data for the semantic model [1]
•  depends on data preparation being done in the data lake [1]
• {benefit} enables high-performance interactive analysis [1]
•  ideal for semantic models connecting to large data sources
• e.g. lakehouse, warehouse and other data source with Delta tables [1]
• performance depends on well-tuned Delta tables [1]
•  {benefit} performance
• normally outperforms DirectQuery queries when loading and interacting with visuals in reports [1]
• {benefit}maximizes ROI because 
• analyzed data volumes can exceed capacity's max memory limits [1]
• only the needed data to answer a query is loaded into memory [1]  
• {benefit} minimizes data latencies 
•  makes new data available without refresh schedules [1]
• quickly and automatically synchronizing a semantic model with its sources [1]
• {operation} refresh 
• low-cost operation that analyzes the metadata of the latest version of the Delta tables and updates references to the latest files in OneLake
• DL moves data preparation to OneLake
• ⇐ uses the full breadth of Fabric technologies for data prep
• incl. Spark jobs, T-SQL DML statements, dataflows, pipelines, etc.
• {operation} transcoding
• the process of loading data from OneLake only when a query requests a column for the first time 
• {action} the semantic model receives a DAX (or MDX) query 
• {step} determines what columns are needed to produce the result
• {step} determines which columns are already resident in memory [2]
• {step} loads the missing columns from OneLake [2]
•  typically a fast operation, however it can depend on factors 
•  e.g. the cardinality of data stored in the columns
•  remains resident until there's reason for it to be removed (evicted) from memory
• {reason} the model or table was refreshed after a Delta table update at the source [2]
• {reason} no query used the column for some time [2]
• {reason} other memory management reasons [2]
•  e.g. memory pressure in the capacity due to other, concurrent operations [2]
•  SKU determines the maximum available memory for each Direct Lake semantic model on the capacity [2]
• {operation} framing 
• an operation triggered by a refresh of a semantic model [2]
• it usually takes only a few seconds to complete
• ⇐ because it's a low-cost operation where the semantic model analyzes the metadata of the latest version of the Delta Lake tables and updates to reference the latest Parquet files in OneLake [2]
• the process might evict resident table column segments and dictionaries from memory if the underlying data changed [2]
• the point in time of the refresh becomes the new baseline for all future transcoding events [2]
• DL queries only consider data in the Delta tables as of the time of the most recent framing operation [2]
• DL tables are queried to return data based on the state of the Delta table at the point of the most recent successful framing operation [2]
• that time isn't necessarily the latest state of the Delta tables [2]
• the semantic model analyzes the Delta log of each Delta table during framing to drop only the affected column segments and to reload newly added data during transcoding [2]
• {optimization} dictionaries usually aren't dropped when incremental framing takes effect, and new values are added to the existing dictionaries [2]
• helps reduce the reload burden and benefits query performance [2]
• when a Delta table receives no updates, no reload is necessary for columns already resident in memory [2]
•  queries show far less performance impact after framing because incremental framing essentially enables the semantic model to update substantial portions of the existing in-memory data in place [2]
• can fail if a Delta table exceeds the Fabric capacity guardrails [2]
•  e.g. when a Delta table has more than 10,000 parquet files [2]
•  {architecture} Direct Lake on OneLake
•  isn't coupled with the SQL analytics endpoint [2]
•  offers tighter integration with OneLake features such as OneLake security [2]
•  offers more efficient DAX query plans because [2]
• ⇐ isn’t required to check for SQL based security [2]
•  fallback isn't supported [2]
• {architecture} Direct Lake on SQL analytics endpoint
•  a DAX query might use DirectQuery fallback [2]
•  involves seamlessly switching to DirectQuery mode [2]
•  DirectQuery fallback
• retrieves data directly from the SQL analytics endpoint of the lakehouse or the warehouse [2]
•  fallback occurs when SQL based security is detected in the SQL analytics endpoints
•  a DirectQuery operation sends a query to the SQL analytics endpoint. Fallback operations might result in slower query performance [2]
•  {setting} Keep your Direct Lake data up to date
• performs automatic updates of Direct Lake tables [2]
• via semantic model settings >> Refresh 
• {enabled|default} ensures that data changes in OneLake are automatically reflected in the Direct Lake semantic model [2]
•  the semantic model performs a framing operation whenever data modifications in the underlying Delta tables are detected [2]
• {recommendation} leave the setting on, especially when you have a small or medium-sized semantic model
• useful in case of low-latency reporting requirements and Delta tables are modified regularly [2]
•  {disabled} it might be needed to disable automatic updates to allow completion of data preparation jobs or an ETL process before exposing any new data to consumers of the semantic model [2]
•  users can trigger a refresh manually in the Fabric portal [2]
•  via refresh schedule, or programmatically by using the Power BI REST API or TOM [2]
• 
  

References: 
[1] Microsoft Learn (2026) Direct Lake overview [link]
[2] Microsoft Learn (2026) 

Acronyms:
API - Application Programming Interface
DL - Direct Lake 
MDX - Multidimensional Expressions
MF - Microsoft Fabric
REST
SKU - Stock Keeping Unit
TOM - Tabular Object Model
T-SQL - Transact SQL

 

⚡️🗒️Power BI: ALLSELECTED in DAX [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 13-Oct-2026

[DAX] ALLSELECTED()

• {def} function that returns all the rows in a table, or all the values in a column [1]
• ignores any filters that might have been applied inside the query [1]
•  removes the corresponding filters from the filter context, restoring the last shadow filter context
• keeps filters that come from outside [1] 
• gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters 
• does not materialize the resulting table when called directly in a filter argument of CALCULATE or CALCULATETABLE 
• via ALLSELECTED([<tableName> | <columnName>[, <columnName>[, <columnName>[,…]]
• {parameter|optional} tableName
• the name of an existing table 
• cannot be an expression
• {parameter|optional} columnName
• the name of an existing column using standard DAX syntax, usually fully qualified
• cannot be an expression 
• if there is one argument, the argument is either tableName or columnName [1]
• if there is more than one argument, they must be columns from the same table [1] 
• {scenario} used to obtain visual totals in queries [1] 
• {scenario} used as a table expression when it has at least one argument [2] 
• {scenario} used without arguments only as a CALCULATE or CALCULATETABLE modifier [2]  
• {distinction} different from ALL() because 
• it retains all filters explicitly set within the query [1] 
• it retains all context filters other than row and column filters [1] 
• {limitation}[DirectQuery mode] not supported when used in calculated columns or RLS rules [1] 
• only DAX function that leverages shadow filter contexts [3] 
• has a slightly different behavior when
• used in SUMMARIZECOLUMNS 
• mostly produces the expected result [3] 
• removes filters for the currently-iterated group-by column, restoring the original filter set by SUMMARIZECOLUMNS itself [3] 
• SUMMARIZECOLUMNS  can be used in nearly all the visuals [3] 
• inside an iterator
• {warning} can produce weird results [3] 
• does not return the values of a column (or table) filtered outside a visual [3] 
• displays different behaviors, depending on whether it is used in SUMMARIZECOLUMNS or not, and on the presence of shadow filter contexts [3]
• {best practice} don’t create a measure that uses ALLSELECTED within an iteration [3]
•  it can produce unexpected results, poor performance, and difficult-to-understand calculations.
• {best practice} minimize the use of measures that contain ALLSELECTED [3]
• can be safely used only in measures that are directly placed in the reports [3] 
•  

Previous Post <<||>> Next Post

References: 
[1] Microsoft Learn (2026) DAX: ALLSELECTED [link]
[2] DAX Guide (2026) ALLSELECTED DAX Function (Filter) SQL[link]
[3] SQLBI (2026) ALLSELECTED Best Practices [link]

💎🏭SQL Reloaded: QUALIFY in Microsoft Fabric (Before/After) 🆕

One recent addition to T-SQL is the QUALIFY clause, which filters rows returned by a query based on a search condition that can reference window (analytic) functions [1]. The QUALIFY clause allows filtering directly in the block in which the window function is defined. According to the documentation QUALIFY can be used directly after HAVING clause:

SELECT select_list
FROM   table_source
[ WHERE <search_condition> ]
[ GROUP BY group_by_specification ]
[ HAVING <search_condition> ]
[ QUALIFY <filter_condition> ]
[ ORDER BY <order_by_expression> [ , ...n ] ]
[ FOR JSON <options> ]

So, for example, if we consider a fictitious Sales Invoices table, to retrieve the customers who bought a product more than once, we had to apply the constraint outside the block in which the window function was defined:

-- product bought more than once by same customer (before)
SELECT *
FROM (-- calculating resultset
	SELECT customer_id
	, product_id
	, SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity
	FROM [Sales].[sales_invoices]
) DAT
WHERE total_quantity>1

Now, a constraint can be applied directly in the block with the window function via QUALIFY followed by one or more constraints:

-- product bought more than once by same customer (after) 
SELECT customer_id
, product_id
, SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity
FROM [Sales].[sales_invoices]
QUALIFY SUM(quantity) OVER(PARTITION BY invoice_id, product_id)>1

Given the order in which the constraints are processed, QUALIFY allows to reduce redundancy by referencing a new column defined in the SELECT statement (update 19-Oct-2026):

-- product bought more than once by same customer (after) 
SELECT customer_id
, product_id
, SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity
FROM [Sales].[sales_invoices]
QUALIFY total_quantity>1

Hopefully this is available on all supported platforms. Otherwise, the logic must be repeated if a second constraint is needed for checking whether the value is within a boundary. Upon case, the logic can become even more complex (e.g. multiple intervals are checked):

-- product bought more than once by same customer (after) 
SELECT customer_id
, product_id
, SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity
FROM [Sales].[sales_invoices]
QUALIFY SUM(quantity) OVER(PARTITION BY invoice_id, product_id)>1

  AND SUM(quantity) OVER(PARTITION BY invoice_id, product_id)<10

Imagine how complex can become a query like the following (update 19-Oct-2026):

-- product bought more than once by same customer with interval testing
SELECT customer_id
, product_id
, SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity
FROM [Sales].[sales_invoices]
QUALIFY (total_quantity>1 AND total_quantity)<10)

    OR (total_quantity>21 AND total_quantity)<30)

    OR (total_quantity>41 AND total_quantity)<50)

    OR (total_quantity>61 AND total_quantity)<70)  

Notes:
1) Having fewer blocks of code improves in theory code's readability and hopefully maintainability when the code doesn't need to be replicated as in the last example. This approach should improve also performance, however further tests are needed to validate this. 
2) It's useful to include the same logic used in QUALIFY also in the main SELECT statement, at least for troubleshooting purposes. 
3) The syntax seems to be supported only in Fabric warehouse, respectively SQL Endpoints for warehouse, lakehouse and SQL database.4) QUALIFY clause requires at least one window function in the SELECT list, ORDER BY list or QUALIFY expression (see Msg 8826, Level 16).
4) QUALIFY clause requires at least one window function in the SELECT list, ORDER BY list or QUALIFY expression (see Msg 8826, Level 16). (update 19-Oct-2026)
5) One should check also whether the syntax is supported by all the features. For example, it's not supported by UPDATE and DELETE statements in warehouse. The restrictions for SQL Endpoints should be considered as well.
6) Developers must consider also portability of code when it's the case.
7) According to Copilot, QUALIFY is supported in Teradata (originator of the feature), Snowflake, Google BigQuery, Amazon Redshift, Databricks SQL, DuckDB and ClickHouse. (update 19-Oct-2026)
8) Of course, the window functions can be mixed to address different business questions, though it's maybe a good idea to move the DENSE_RANK in SELECT and reference the new column within QUALIFY (update 19-Oct-2026):

-- first/last customer by product incl. total
SELECT product_id
, SUM(quantity) OVER(PARTITION BY product_id) quantity_total
, FIRST_VALUE(customer_id) OVER(PARTITION BY product_id ORDER BY invoice_date ASC) first_status
, LAST_VALUE(customer_id) OVER(PARTITION BY product_id ORDER BY invoice_date DESC) last_status
FROM [LH_CRM_Data].ERP.[sales_invoices_partitioned4]
QUALIFY DENSE_RANK() OVER(PARTITION BY product_id ORDER BY invoice_date)=1

 9) QUALIFY can be used also in combination with a CROSS/OUTER APPLY. For example, to retrieve the latest invoice details one could write a query like the one below (update 02-Nov-2026):

-- OUTER APPLY + QUALIFY
SELECT top 10 CST.CustomerID
, CST.CompanyName
, DAT.*
FROM [SalesLT].[Customer] CST
   OUTER APPLY (
   SELECT SalesOrderID
   , [ShipDate]
   , ROW_NUMBER() OVER (PARTITION BY SOH.CustomerID ORDER BY ShipDate) Ranking
   FROM [SalesLT].[SalesOrderHeader] SOH
   WHERE CST.CustomerID = SOH.CustomerID
   QUALIFY Ranking = 1
   ) DAT
--WHERE DAT.SalesOrderID IS NOT NULL -- testing
ORDER BY CST.CompanyName

It can be surprising that it works (at least I was positively surprised).
Instead of OUTER APPLY one could use a LEFT JOIN (the two versions return the same results):

-- LEFT JOIN + QUALIFY
SELECT top 10 CST.CustomerID
, CST.CompanyName
, DAT.*
FROM [SalesLT].[Customer] CST
   LEFT JOIN (
   SELECT SalesOrderID
   , CustomerID
   , [ShipDate]
   , ROW_NUMBER() OVER (PARTITION BY SOH.CustomerID ORDER BY ShipDate) Ranking
   FROM [SalesLT].[SalesOrderHeader] SOH
   QUALIFY Ranking = 1
   ) DAT
   ON CST.CustomerID = DAT.CustomerID
--WHERE DAT.SalesOrderID IS NOT NULL -- testing
ORDER BY CST.CompanyName

Happy coding!

References:
[1] Microsoft Learn (2026) SELECT - QUALIFY clause (Transact-SQL) [link]