IGLOO BI

In Power BI, the most powerful and simultaneously the most misunderstood function is undoubtedly CALCULATE . Many users operate under the following assumption: “Since it is inside CALCULATE, the SUM will be calculated first, and then the filter will be applied, right?” To put it bluntly, it is exactly the opposite. CALCULATE in DAX does not execute procedurally. In other words, it is not structured to execute "from top to bottom" like C or Python; rather, it is a method where filters are defined first → the environment is reconstructed → and then the expression is evaluated upon that foundation. Therefore, it is more accurate to understand CALCULATE not merely as a function, but as an "engine that redesigns the calculation environment." In this post, we will precisely organize the internal execution order (timeline) of CALCULATE based on actual example data. ...

When using the FILTER function inside CALCULATE , deciding whether to set the stage with ALL or VALUES is a critical design choice. It determines whether you will "acknowledge the authority of external slicers or completely ignore them." By comparing the two codes side-by-side, let us analyze how the results of independent evaluations within the engine differ. 1. Example Data for Analysis: SalesData (Fact Table) This data contains 20 transactions that occurred during a single day. Date Time ID ProductName Category SalesAmount 2024-01-01 8:30 C01 Americano Beverage 45 2024-01-01 8:45 C02 Cafe Latte Beverage 8 2024-01-0...

When working with Power BI, you will eventually encounter a moment where you ask: "It's clearly the same logic... so why are the results different?" Specifically, the experience of getting completely different results depending on whether you used a VAR or not is an almost essential rite of passage for practitioners. This issue is not a simple difference in syntax; it is deeply connected to the Evaluation Timing of the DAX engine. This article clarifies this difference and explains structurally why results vary inside and outside of CALCULATE. 1. The Essence of the Problem: "Same Code, Different Results" Let’s compare the following two codes. The goal is to calculate the 'Average of the last 4 months from the current point.' 1) Case 1: Direct Evaluation Inside CALCULATE (Normal Operation) This method works exactly as we intended, escaping the influence of the sl...

1. What is SELECTEDMEASURE()? Simply put, it is a "placeholder" that refers to the "very measure currently being calculated." Normally, when writing DAX, you explicitly use the name of a measure, such as [Total Sales]. However, within Dynamic Format Strings or Calculation Groups, it is impossible to know in advance which measure will be used. In these cases, SELECTEDMEASURE() acts as a command that says, "Whichever measure comes in, fetch its value first!" • Usage: Dynamic Format Strings, Calculation Groups • Characteristics: Since you don’t need to hard-code measure names, the reusability of your code is maximized. 2. Why is SELECTEDMEASURE() a 'Revolution'? Suppose a company report has 50 different metrics, and you need to apply a rule to every single one of them: "If the value is 1 million or more, attach an 'M' unit; if it is 1,000 or more, attach a 'K' unit." ...

Many analysts struggle with distorted YoY reports when launch dates differ across years. According to search queries frequently identified in Google Search Console , BI professionals are increasingly seeking ways to 'align disparate timelines' rather than relying on standard calendar dates. The most frequent error found on a data analyst's desk? Ironically, it is "comparing data based on exact calendar dates." While it may seem precise, it is actually the method that creates the most significant distortions. In this post, we solve this "Calendar Trap" by implementing an Event-based Day Index . Using a robust DAX architecture, we will move beyond simple YoY limitations to measure your business's true growth velocity and momentum from a 'Zero Point' perspective. 1. Date as a Record, Index as a Standard The most dangerous trap in data analysis is compari...