18 November 2026

🎯Derar Alhussein - Collected Quotes

"A data lakehouse represents a hybrid solution that combines the best aspects of data lakes and data warehouses. Specifically, it integrates the openness, scalability, and cost efficiency of data lakes with the reliability, strong governance, and performance features of data warehouses." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025)

"A data stream represents an unbounded flow of data, often originating from various sources such as sensors, log files, or social media platforms. As new data is generated, it is appended to the stream, making it a dynamic and constantly changing dataset." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025) 

"At the top of the architecture is the Databricks workspace, which serves as the user interface for interacting with the platform. It provides an interactive environment where users can perform data engineering, analytics, and AI workloads using a variety of languages, such as Python, SQL, R, and Scala. The workspace offers a range of services, including notebooks for development, dashboards for visualizing data, and workflow management tools for orchestrating data pipelines." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025)

"Clusters in Databricks form the backbone of data processing and analytics on the platform. A cluster is essentially a collection of computers, often referred to as nodes, instances, or virtual machines, working together as a single entity. In the context of Apache Spark, which powers Databricks, a cluster comprises a master node known as the driver and several worker nodes [...]. The driver node is primarily responsible for orchestrating the activities of the worker nodes, which execute tasks in parallel, thereby enabling efficient processing of large-scale data." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025)

"Data ingestion [...] refers to the process of loading data from files into Delta Lake tables. One of the significant challenges in data ingestion is the need to avoid reprocessing the same files multiple times. In a traditional data pipeline, each time the pipeline is run, it would reprocess all the files, including those that have already been ingested previously. This approach can be computationally expensive, time-consuming, and can lead to additional deduplication work, especially when dealing with large datasets, and this is where incremental data ingestion comes into play." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025)

"Databricks notebooks are interactive development environments that enable you to write, debug, and execute code in a collaborative setting. These notebooks offer advanced capabilities that extend beyond those of traditional environments like Jupyter Notebooks. Databricks notebooks support multiple programming languages, including Python, SQL, Scala, and R. In addition, they integrate seamlessly with Spark clusters, allowing users to leverage distributed computing resources directly from the notebook interface." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025)

"Databricks Runtime is a pre-configured virtual machine image optimized for use within Databricks clusters. It includes a set of core components, such as Apache Spark, Delta Lake, and other essential system libraries. Delta Lake enhances traditional data lakes by providing transactional guarantees similar to those found in operational databases, thereby ensuring improved data reliability andconsistency." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025)

"In case of failures, such as node crashes or network issues, the streaming engine is capable of resuming processing from where it left off. This is achieved through the combination of checkpointing and a mechanism called write-ahead logs. They enable capturing the offset range corresponding to the data being processed during every trigger, which makes it possible to recover from failures without any data loss. It’s important to note that this guarantee mainly depends on the repeatability of the data sources. Data sources such as cloud-based object storage or pub/sub messaging services are typically repeatable, meaning that the same data can be read multiple times if needed. This allows the streaming engine to restart or reprocess the data under any failure condition." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025) 

"In Structured Streaming, the key idea is to handle live data streams as unbounded, continuously growing tables, where each incoming data item is appended as a new row [...]. This design allows you to apply familiar SQL and DataFrame operations on streaming data in the same way you would with batch data. By unifying batch and streaming operations, Structured Streaming eliminates the need for separate technology stacks and facilitates the migration of your existing batch Spark jobs to streaming jobs." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025)

"It’s crucial to be aware of the limitations associated with tables having foreign data sources - they are not Delta tables. This means that the performance benefits and features offered by Delta Lake, such as time travel and guaranteed access to the most recent version of the data, are not available for these tables. This limitation becomes especially noticeable when dealing with large database tables, potentially leading to performance issues." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025)

"Partitioning can significantly enhance the performance of large Delta tables by facilitating efficient data retrieval. However, it’s important to note that for small to medium-sized tables, the benefits of partition may be negligible or outweighed by drawbacks. One significant drawback is the potential emergence of what is known as the 'small files problem'. This problem arises when data partitioning results in the creation of numerous small files, each containing a relatively small amount of data. While partitioning aims to improve query performance by reducing the amount of data scanned, the presence of many small files can prevent file compaction and efficiency in data skipping. In general, partitioning should be selectively applied based on the size and nature of the data." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025)

"Spark Structured Streaming is a scalable stream processing engine that revolutionizes the way data streams are processed and queried. It enables querying of infinite data sources, automatically detecting new data as it arrives and persisting results incrementally into target data sinks [...]. A sink is often a durable storage system such as files or tables that serves as the destination for the processed data." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025)

"Time travel is a feature in Delta Lake that allows you to retrieve previous versions of data in Delta Lake tables. The key aspect of Delta Lake time travel is the automatic versioning of the table. This versioning provides an audit trail of all the changes that have happened on the table. Whenever a change is made to the data, Delta Lake captures and stores this change as a new version. Each version represents the state of the table at a specific point in time."(Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025)

"What makes Delta Lake an innovative solution is its ability to overcome such challenges posed by traditional data lakes. Delta Lake provides ACID transaction guarantees for data manipulation operations in the lake. It offers transactional capabilities that enable performing data operations in an atomic and consistent manner. This ensures that there is no partially committed data; either all operations within a transaction are completed successfully or none of them is. These capabilities allow you to build reliable data lakes that ensure data integrity, consistency, and durability." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025)

"While data lakes are excellent solutions for storing massive volumes of diverse data, they often encounter several challenges related to data inconsistency and performance issues. The primary factor behind these limitations is the absence of ACID transaction support in a data lake. ACID stands for atomicity, consistency, isolation, and durability, and represents fundamental rules that ensure operations on data are reliably executed, as in traditional databases. This absence led to issues such as partially committed data and corrupted files, ultimately affecting the overall reliability of the data stored in the lake." (Derar Alhussein, "Databricks Certified Data Engineer Associate Study Guide", 2025)

🤖Prompt Engineering: Problems (Just the Quotes)

"Chain-of-thought prompting is a method that forces LLMs to reason through a series of steps, resulting in more structured, transparent, and precise outputs. The goal is to break down complex tasks into smaller, interconnected subtasks, allowing the LLM to address each subtask in a stepby-step manner. This not only helps the model to 'focus' on specific aspects of the problem, but also encourages it to generate intermediate outputs, making it easier to identify and debug potential issues along the way. Another significant advantage of chain-of-thought prompting is the improved interpretability and transparency of the LLM-generated response. By offering insights into the model’s reasoning process, we, as users, can better understand and qualify how the final output was derived, which promotes trust in the model’s decision-making abilities." (Sinan Ozdemir, "Quick Start Guide to Large Language Models: Strategies and Best Practices for Using ChatGPT and Other LLMs", 2024) 

"Another problem that can be confusing is that LLMs seldom put out the same thing twice. [...] Traditional databases are straightforward - you ask for something specific, and you get back exactly what was stored. Search engines work similarly, finding existing information. LLMs work differently. They analyze massive amounts of text data to understand statistical patterns in language. The model processes information through multiple layers, each capturing different aspects - from simple word patterns to complex relationships between ideas." (Jeremy C Morgan, "Coding with AI: Examples in Python", 2025)

"AI isn’t just going to be about our digital world. It’s also about our physical world; and applied properly, imagine what AI can do for the pace of discovery and innovation. It’s not just makeup; imagine what it can do for new materials discovery for medicine, energy, climate, and all the other pressing challenges we face as a species - these are the same challenges of makeup, just described with a different 'language'. And quantum computing evolves, we’re bound to see a synergy of these innovations that we can use to tackle these problem domains and more." (Rob Thomas et al, "AI Value Creators: Beyond the Generative AI User Mindset", 2025)

"Despite their impressive capabilities, LLMs are not without limitations. One of the most significant challenges is the problem of hallucination, where an LLM generates factually incorrect or misleading information that appears plausible. This is particularly problematic in domains requiring high factual accuracy, such as healthcare, finance, and legal applications. To mitigate hallucinations and enhance the reliability of LLM outputs,  Retrieval-Augmented Generation (RAG) has emerged as a powerful technique. RAG works by dynamically retrieving relevant information from an external knowledge source (such as a knowledge graph) at inference time, rather than just relying on pre-trained knowledge. This approach ensures that the model has access to up-to-date and accurate data, grounding answers in verified information rather than generating content purely from its internal representations." (Aldo Marzullo et al, "Graph Machine Learning" 2nd Ed., 2025)

"The same difficulties that characterize training deep feedforward networks also apply to RNNs; gradients tend to die out over long distances using traditional activation functions (or explode if the gradients become greater than 1). However, unlike feedforward networks, RNNs aren’t trained with traditional backpropagation, but rather a variant known as Backpropagation through Time (BPTT): the network is unrolled, as before, and backpropagation is used, averaging over errors at each time point (since an 'output', the hidden state, occurs at each step). Also, in the case of RNNs, we run into the problem that the network has a very short memory; it only incorporates information from the most recent unit before the current one and has trouble maintaining long-range context. For applications such as translation, this is clearly a problem, as the interpretation of a word at the end of a sentence may depend on terms near the beginning, not just those directly preceding it." (Joseph Babcock & Raghav Bali, "Generative AI with Python and PyTorch" 2nd. Ed., 2025)

"Transformers are complex models built like LEGO blocks using multiple smart and specialized components. [...] Briefly, a vanilla transformer model consists of separate stacks of encoders and decoders. Each encoder block includes multi-head self-attention, enabling the model to capture relationships between tokens regardless of their positions. Residual connections help maintain gradient flow, preventing the vanishing gradient problem. Layer normalization ensures training stability, and feed-forward layers introduce non-linearity and learn complex token interactions. Decoder blocks contain the same components but also include an encoder-decoder attention mechanism to incorporate context from the encoder. The model uses embedding layers to convert tokens into a continuous latent space for contextual learning and positional encoding to preserve the order of tokens in the sequence." (Joseph Babcock & Raghav Bali, "Generative AI with Python and PyTorch" 2nd. Ed., 2025)

"When there are hidden layers between the input and output, the problem becomes more complex: when do we change the internal weights to compute the activations that feed into the final output? How do we modify them in relation to the input weights? The insight of the backpropagation technique is that we can use the chain rule from calculus to efficiently compute the derivatives of each parameter of a network with respect to a loss function and, combined with a learning rule, this provides a scalable way to train multilayer networks." (Joseph Babcock & Raghav Bali, "Generative AI with Python and PyTorch" 2nd. Ed., 2025)

"While the backpropagation procedure provides a way to update interior weights within the network in a principled way, it has several shortcomings that make deep networks difficult to use in practice. One is the problem of vanishing gradients. [...] As the value of the sigmoid function increases or decreases toward the extremes (0 or 1, representing either 'off' or 'on' ), the values of the gradient vanish to near zero. This means that the updates to and , which are products of these gradients from hidden activation functions , shrink toward zero, making the weights change little between iterations and making the parameters of the hidden layer neurons change very slowly during backpropagation. Clearly, one problem here is that the sigmoid function saturates; thus, choosing another nonlinearity might circumvent this problem." (Joseph Babcock & Raghav Bali, "Generative AI with Python and PyTorch" 2nd. Ed., 2025)

"A major aspect of the alignment problem is values misalignment: AI systems may lack sufficient understanding of human ethics, cultural norms, or social contexts, making it difficult to translate our values directly into algorithms. For instance, if an AI is programmed to prioritise efficiency without balancing safety or ethical considerations, it might make decisions that, while effective, disregard human welfare. This is further complicated by 'specification gaming', where an AI might exploit loopholes in its programming to achieve objectives in unintended or counterproductive ways. An AI instructed to avoid obstacles, for instance, could redefine what counts as an 'obstacle' and take routes that, while technically following the rule, lead to negative outcomes." (Alan Watkins & G C Cooke, "Smarter than You Winning in Business with Superintelligent AI", 2026)

"As AI systems become more integrated into critical decision-making processes, the 'black box' problem – the inability to understand how a model arrived at a specific output – becomes a major barrier to trust and adoption. For RAG systems, this is particularly crucial; a user needs to know not just the answer, but why the system believes that answer to be true. Transparency and explainability (explainable AI, XAI) are the disciplines focused on making AI reasoning understandable to humans. A transparent RAG system allows users to verify the accuracy of its responses, builds trust by demonstrating a logical process, and enables developers to debug and improve the system. It transforms the AI from an oracle that must be blindly trusted into a tool for augmented intelligence, where the human remains the ultimate arbiter of truth." (Saloni Garg et al, "RAG Artificial Intelligence: Retrieval-Augmented Generation in Generative AI", 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)

"Generative AI has a lot of problems because it needs a lot of data to work. Generative models like GPT and GANs need a lot of training data to learn patterns and make good outputs. This data dependency can cause problems like overfitting, which happens when the model does well on training data but doesn’t work well with new, unseen data. Also, the quality of the content that is generated is directly related to the diversity and representativeness of the training data. This means that biased or incomplete datasets can lead to outputs that are wrong or unfair." (Saloni Garg et al, "RAG Artificial Intelligence: Retrieval-Augmented Generation in Generative AI", 2026)

"MCP is an open standard that lets AI applications connect to external tools, data sources, and workflows through a consistent, discoverable interface. In practice, MCP enables LLM-powered hosts, such as IDEs, desktops, and cloud services, to discover capabilities at runtime. These hosts can then invoke tools through a shared schema without requiring bespoke adapters for each integration [..]. MCP addresses the long-standing M×N integration problem: without a common protocol, every model ends up integrating separately with every system. MCP collapses this complexity by standardizing how agents discover, call, and exchange context with external capabilities." (Bennie Haelen, "ML and Generative AI in the Data Lakehouse Building and Deploying AI Applications at Scale", 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)

"[...] RNNs [...] process tokens sequentially, one at a time, and are known to suffer from the long-range dependency problem. This 'forgetfulness' problem is common in RNNs, where information from earlier parts of the input gradually fades, meaning the model quickly forgets the context of the sentence, making it hard for the decoder to produce meaningful output over longer sequences. It’s sort of like short-term amnesia that results in a loss of coherence as tokens pass by." (Robert Barton & Jerome Henry, "Demystifying Generative AI A Practical and Intuitive Introduction", 2026)

"The first big innovation in the transformer architecture is its ability to process tokens in parallel rather than sequentially, one at a time. This was a huge breakthrough, as it allowed faster training and effectively overcame the long-range dependency problem. Rather than reading one sentence at a time as humans do, a transformer reads a whole input sequence at once - spanning hundreds of thousands of words in some cases. To be clear, there are still limitations to how much a transformer can read in parallel, known as the context window, but generally this window is very large. The ability to examine words in parallel allows the transformer to understand how words relate to each other, even if they are far apart in the same sentence, paragraph, or even whole book." (Robert Barton & Jerome Henry, "Demystifying Generative AI A Practical and Intuitive Introduction", 2026)

"The most common form of prompt hacking is prompt injection. This occurs when an attacker embeds malicious instructions inside a seemingly innocuous prompt to override the developer’s intended task. The root of the problem is architectural: current language models treat all text, including system prompts, developer instructions, and user input, as one continuous sequence. There is no built-in notion of 'trusted' versus 'untrusted' input, so a later instruction can supersede an earlier one. The simplest version is direct injection, where the attacker places the malicious directive directly in the user’s input. A classic example exploits the fact that models prioritize more recent or specific instructions [...] Indirect injection hides instructions in external content, such as a web page or document the model is asked to summarize, so the model executes code it wasn’t supposed to see. Specialized variants include code injection, where a code-generation assistant is tricked into outputting malicious commands, and recursive injection, where the output of one model carries an injection to the next in a chain. All of these attacks exploit the model’s inability to separate context from instruction." (Chris Minnick, "A Developer’s Guide to Integrating Generative AI into Applications", 2026)

"The process of deciding which features to include and which ones to ignore is known as feature engineering. It is a critical step in supervised learning. More features can make the model more powerful and accurate, but they also make it more complex, harder to interpret, and slower to train. As in many other parts of AI, deciding the number of features for a particular problem is often a balancing act." (Robert Barton & Jerome Henry, "Demystifying Generative AI A Practical and Intuitive Introduction", 2026)

"The promise of AI is its ability to process information objectively and at scale. However, this promise is fundamentally threatened by the twin challenges of bias and misinformation. AI systems are not born in a vacuum; they are created by humans and trained on data produced by humans. Consequently, they are prone to inheriting and even amplifying our prejudices, errors, and the systemic inequalities present in that data. In a RAG system, this risk is a two-fold problem: first in the retrieval of information, and second in the generation of a response based on that retrieval. A failure to address these issues doesn’t just lead to technically incorrect outputs; it can perpetuate social harm, erode public trust, and lead to the widespread dissemination of falsehoods. Therefore, understanding and mitigating bias and misinformation is not an optional add-on but a core requirement for any ethically deployed AI system." (Saloni Garg et al, "RAG Artificial Intelligence: Retrieval-Augmented Generation in Generative AI", 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)

17 November 2026

🏭🗒️Microsoft Fabric: Capacity Management [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: 16-Nov-2026

[Microsoft Fabric] Capacity Management

  • {def} process for the administration and governance of the compute resources (CUs) available in a Fabric capacity
  • {concept} capacity 
    • {def} a dedicated set of compute resources that are available at a given time to be used
      • each capacity has fixed compute resources measured in CUs [6]
      • defines the ability of a resource to perform an activity or to produce output
      • resides in a specific region of the buyers’ choice where both the data & compute reside [3]
      • provides the compute and storage for all the workspace artifacts [3]
        • workspaces are assigned to a capacity 
    • shared across:
      • workloads
        • a single capacity provides the computing power for all Fabric workloads, and there is no need to allocate compute separately per engine.
      • projects
        • a capacity typically supports dozens of separate projects, each in its own workspace(s).
      • users
        • every user action that consumes compute (e.g. queries, refreshes, pipeline runs, notebook execution) uses the same capacity pool.
    • priced at a fixed hourly rate 
      • multiple capacities can be purchased, deployed and managed by different owners residing in a single tenant 
        • {benefit} allows each business unit to pay for their own consumption [3]
      • based on CUs provisioned [3]
      • dimensioned as a function of cores: 
        • F2=2^1, F4=2^2, ..., F32=2^5, F64=2^6, ..., F2048=2^11 (powers of 2 from 1..11)
        • ⇐ flexible building blocks for growth
        • with increased Fx it provides
          • better concurrency 
          • lower throttling risk
          • more realistic PoCs
          • better workload isolation
          • increased reliability 
      • purchased either as 
        • PAYG model
        • RI resource
        • F0 
  • {concept} Stock Keeping Unit (SKU)
    • {def} the capacity tier that determines how much compute power, memory, and overall performance a Fabric environment receives
  • {concept} capacity unit (CU) 
    • {def} the standardized measure of computing resources available to a Fabric capacity
      • used to abstract away the actual CPU cores, memory, and infrastructure so that customers can size Fabric workloads consistently
        •  measure the compute power available for each SKU [2]
    • CUs consumption is not a hard limit per SKU
      • based on an average over a 24-hour period
        • gives customers the ability to take advantage of bursting and smoothing
  • {concept} timepoint 
    • {def} internal measurement intervals MF uses to evaluate how much CU consumption is occurring over time and whether the capacity is being throttled
      •  24 hours contain 2,880 timepoints
      •  Fabric automatically manages the amount of consumed CUs in each timepoint [2]
      •  the boundaries are aligned to the Fabric service clock
      •  a workload is charged to whichever 30-second timepoint(s) are active when the work executes
  • {concept} overage
    • the amount of capacity debt that would otherwise cause throttling because a capacity has consumed more compute than its provisioned CUs can support after smoothing
      •  when overages exceed the allowed 10-minute throttling window, they become carryforward CUs [2]
        • carryforward CUs apply to each subsequent timepoint [2]
        • if a timepoint isn't full, the unused CUs reduce the carryforward CUs amount (aka burndown)
  • {operation} provisioning (aka capacity provisioning)
    • provisioned with a certain amount of compute units, analogous to CPU cores
    • there is no authoritative guide to help customers calculate the CUs required by a solution
    • {recommendation} experiment to figure out the best fit
      • allows to find the optimum size for workloads  
  • {operation} configuration (aka capacity configuration) 
    • capacities can be configured in endless ways to meet scale, usage and governance requirements while tuning to minimize TCO and performance goals [3]
  • {mechanism} bursting
    • the ability for a Fabric capacity to temporarily consume more compute resources beyond what the customer purchased, respectively beyond what was allocated for a given job
    • {benefit} job acceleration 
      • allows to speed workloads execution
        • e.g. instead of running a job on 64 CUs in 60 seconds, bursting could use 256 CUs to complete in 15 seconds
    • {benefit} use extra resources
      • allows workloads to use additional compute resources to handle spikes in capacity requirements over short periods of time
        • allows jobs to utilize far more resources than 'face value'
      • improves users’ experience
      • provides a temporary relief, until the burst allowance is eventually exhausted
      • {warning} can hide capacity sizing problems
        • because bursting absorbs occasional overloads
        • potential for future throttling, if not addressed timely
    • {characteristic} automatic 
      • automatically allocates resources as needed to execute at maximum performance [3]
        • regardless of SKU
        • {warning} one query could consume all the quota of a single time window and much more! [3]
      • when the system reasons it can accelerate the job by applying extra resources [3]
        • occurs behind the scenes, acting as a buffer
        • no settings are required [3]
  • {mechanism} smoothing (aka averaging)
    • a mechanism that spreads the CU consumption of operations across future time periods [5]
      • instead of scheduling all consumption immediately [5]
      • run when small usage bursts occur, flattening compute usage over time to help customers avoid being penalized with additional cost [5]
        • operation's utilization type determines the number of timepoints that Fabric uses for smoothing [2]
          • interactive operations are smoothed over a minimum of five minutes, and up to 64 minutes depending on how much CU usage they consume [2]
          • background operations are smoothed over a 24-hour period because [2]
            • they typically have long runtimes and large CU consumption [2]
      • ensures users can enjoy consistently fast performance without throttling [2]
        • only a portion of the CU usage for an operation applies to any individual timepoint, which reduces throttling overall [2]
        •  smoothed CU usage accumulates as operations run [2]
        • future capacity (CUs available in future timepoints) pays for smoothed usage [2]
          • because the capacity runs continuously [2]
    • bursting and smoothing work together
      • let capacities self-manage and self-heal when temporary spikes in usage would otherwise cause other systems to fail or slow down [2]
      • aren't supported when Autoscale Billing for Spark is enabled [2]
        • Spark usage operates in a PAYG mode [2]
    • {benefit} workload stabilization
      • helps capacities self-stabilize by flattening loads with large spikes into a smooth load profile, eliminating temporal spikes [3]
      • pays back that additional compute consumption when the capacity is less in demand 
        • prevents overloads as the smoothing mechanism will always flatten the resource burst [3]  
    • {benefit} scheduling contention elimination
      • large/scheduled jobs are smoothed over 24 hours [5]
        • eliminates the need to decide the timing and order of job execution [5]
        • operations are always executed for maximum performance [5]
    • {benefit} bad actor protection
      • prevents a single actor (user) with a very demanding query from hogging the entire capacity [5]
        • interactive operations are smoothed over several minutes [5]
  • {mechanism} throttling 
    • {def} protection mechanism that activates when a capacity resource consumption exceeds the capacity threshold for the current time window [5]
      • there can be a short delay before throttling begins [6]
      • running too many operations can overload a capacity [2]
        • the capacity automatically throttles new operations when it's overloaded [2]
      • happens in progressive steps [2]
        • helps to minimize the impact on important tasks like data refreshes [2]
      • not applied automatically when a capacity is operating above 100% utilization [2]
        •  see overage protection
      • an indicator that the capacity is experiencing sustained pressure [5]
        • while one capacity, or set of workspaces, might experience reduced performance from being overloaded, other capacities might continue running normally [2]
      • too much throttling can result in a degraded end-user experience [2]
      • applied at the capacity level [2]
    • starts when a capacity uses up all its CU resources for the next 10 minutes, when overage protection ends [2]
      •  
      • {phase} applies 20-second delays to new interactive operations [2]
      • {phase} rejects new interactive operations when a capacity uses up all its CU resources for the next one hour [2]
        • background operations can start and run [2]
      • {phase} rejects all new requests, interactive and background, when the capacity uses up all its available CU resources for the next 24 hours [2]
        •  the capacity continues to throttle requests until you pay off the consumed CUs [2]
      •  its enforcement continues until unused capacity pays off all carryforward CUs [2]
    • consistently high throttling levels indicate the need to either
      • load balance across multiple capacities [2]
      • increase the capacity SKU size [2]
    • {concept} throttling policies
      • {def} rules Fabric uses to slow down or reject workload execution when a capacity is overloaded
      • prevent a capacity from becoming unusable when its smoothed consumption exceeds the available Capacity Units (CUs).
      • refined to deliver multiple benefits [3]
    • reduced throttling for capacities that only experience occasional spikes [3]
    • [monitoring] Fabric capacity metrics app cane be used to confirm throttling
      • via Compute page >> System events table 
        • shows the history of t4hrottling events [2]
      • via Compute page >> Throttling charts 
        • show when smoothed usage exceeds one of the throttling limits [2]
  • {feature} capacity overage (aka overage protection)
    • {def} feature that allows a capacity to continue running when it would otherwise be throttled because the purchased CU where exceeded
  • {feature} surge protection
    • {def} mechanism that helps prevent a Fabric capacity from becoming overloaded by proactively limiting background workloads before the capacity reaches severe throttling
      • interactive operations smoothed over several minutes, preventing a single user with a very demanding query from hogging the entire capacity [3]
  • {capacity size} F0
    • a zero-provisioned capacity SKU 
      • announced at FabCon Europe Sep, 2026
    • {characteristic} billed on demand
      • no upfront compute charge
      • accommodates one billing category workloads
        • e.g. Data Warehouse
      • designed to work with on-demand billing 
        • rather than requiring customers to reserve compute capacity upfront
    • {characteristic} built-in monitoring
      • via Capacity Metrics >> 'On-demand compute' tab⠀
    • {characteristic} supports no smoothing
      • priced as a multiplier of PAYG
        • prices yet to be disclosed 
    • {advantage} start small, scale on demand
    • {disadvantage} steep learning curve
      • especially when no trial capacity is available
    • {scenario} development & testing with minimal workloads
    • {scenario} small proof-of-concepts
    • {scenario} sporadic workloads
    • {scenario} organizations wanting consumption-based billing
  • {capacity size} F2
    • entry-level capacity with access to standard Fabric features
    • best suited for lightweight, low‑volume, or exploratory workloads that don't require parallelism 
      •  useful to prove that solutions work
      • {scenario} proof-of-concepts
      • {scenario} individual usage
      • {scenario} training & testing with minimal workloads
    • can occasionally execute workloads larger than its nominal size
      •  e.g. small notebooks, warehouse queries may complete successfully 
    • {limitation} many workloads require at least F4 
      • e.g. Spark notebooks, Dataflows, Power BI refreshes), while bursting and smoothing provides less benefit; 
    • {limitation} not viable for production environments
      • very limited compute power
      • throttling occurs often 
      • poor fit for concurrent access 
      • limited refresh & ETL throughput 
  • {capacity size} F4
    • first usable production‑grade capacity for light to moderate workloads
      • ideal for PoCs, small production workloads, notebooks, though workloads may run soon into its limitations
    • allows to run concurrent activities
      • e.g. combined BI & Data Engineering 
    • includes all standard F-SKU features (Copilot, Managed Private Endpoints, etc.)
    • can support 
      • Power BI reports
      • small lakehouses
      • Data Factory pipeliness 
  • {capacity size} F8
    • generally considered strong enough for multiple developers, analysts, and BI users working at the same time
    • often support dozens to hundreds of actively used reports 
    • provides a more realistic environment for testing
      • scheduled refreshes 
      • concurrent users
      • automated pipelines
      • engineering workloads
  • {capacity size} F16
    • works well if workloads are light
    • often support dozens to hundreds of actively used reports
    • good for
      • smaller lakehouses
      • scheduled pipelines
      • moderate notebook execultion 
  • {capacity size} F32
    • suitable for mid-sized workloads, though it may be still insufficient for heavy workloads
    • often support hundreds to thousands of actively used reports.
    • good for
      • departmental BI
      • smaller semantic model
      • limited refresh schedules
    • best for
      • departmental Fabric deployments
      • medium-sized organizations
      • initial production rollouts
      • single-team ownership
  • {capacity size} F64
    • first high‑performance, enterprise‑grade Fabric capacities
      • it's designed for advanced workloads, multiple teams, and high‑throughput data operations
    • with it the licensing model for Power BI changes
      •  equivalent to a Power BI Premium-sized capacity
        •  ⇒ free Power BI user consumption 
          •  ⇐ a license is still needed for development
    • often support hundreds to thousands of actively used reports
    • often where organizations begin running
      • larger Spark workloads
      • multiple engineering team
      • more complex medallion architectures
      • heavy ingestion schedule
    • often the capacity where organizations start implementing
      • workspace isolation strategies
      • capacity governance
      • chargeback/showback
      • surge protection policies
      • dedicated engineering and BI workloads
  • {capacity size} F128+ 
    • ideal for scenarios in which multiple business units share a capacity
    • provides more flexibility in workload scheduling
    • can support very large enterprise estates with thousands of reports
    • governance becomes more important than performance 
  • {type} trial capacity
    • automatically created when a user begins to work with Fabric items in the Fabric portal
    • users are prompted to start the trial capacity [4] 
    • users using a non-Fabric-enabled workspace and who are trying to create Fabric-specific item for the first time, are automatically upgraded to a trial license for MF [4]
    • has a limitation of five trial capacities per tenant [4]
    • users can opt out between a F4 and F64 capacity
    • doesn't have access to all features
      • e.g. Copilot requires at least a F2 capacity
    • valid for 60 days from activation
      •  
  • {feature} Microsoft Fabric Capacity Metrics app 
    • allows monitoring the CUs usage to better understand how many CUs are required for operating your solution and whether you might want to consider scaling up or scaling out your Fabric SKU(s)

Previous Post <<||>> Next Post 

References:
[1] Microsoft Learn (2025) Manage your capacities in the Admin portal [link]
[2] Microsoft Learn (2026) Understand the Fabric capacity throttling policy [link]
[3] Benni De Jagere (202x) Understanding Fabric Capacities
[4] Brian Bønk & Valerie Junk (2026) Microsoft Fabric Analytics Engineer Associate Study Guide: Hands-On Practice and Expert Tips for Acing the DP600 Certification Exam
[5] Microsoft Learn (2026) Microsoft Fabric: Advanced Real-Time Intelligence
[6] Capacity overage in Microsoft Fabric [link]

Acronyms:
BI - Business Intellgience
CI/CD - Continuous Integration and Continuous Deployment
CU - Capacity Unit
IDE - Integrated Development Environments
MF - Microsoft Fabric 
PAYG - Pay-As-You-Go
SKU - Stock Keeping Unit 

31 October 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
  • {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


 

28 October 2026

⚡️🗒️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

27 October 2026

⚡️🗒️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
 

13 October 2026

🏭🗒️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] 
  •  

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]

12 October 2026

💎🏭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]

Related Posts Plugin for WordPress, Blogger...

About Me

My photo
Koeln, NRW, Germany
IT Professional with more than 25 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.