Showing posts with label measures. Show all posts
Showing posts with label measures. Show all posts

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

13 October 2026

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

28 August 2025

🤖〽️Prompt Engineering: Copilot Unabridged (Part 81: Keeping Up with the Machine - How Humans Can Match AI’s Growth)

Prompt Engineering Series

Prompt Engineering Series

Prompt: "write a post of 600 words on what human can do to match AI's growth"

Introduction

Artificial Intelligence (AI) is growing at a staggering pace - learning faster, scaling broader, and evolving in ways that challenge our understanding of intelligence itself. As AI systems begin to outperform humans in narrow tasks and edge closer to general capabilities, the question arises: Can humans keep up? And if so, how?

Matching AI’s growth isn’t about competing with machines on raw processing power. It’s about leveraging our uniquely human strengths, adapting our systems, and evolving our mindset. Here’s how we can rise to the challenge.

1. Embrace Lifelong Learning

AI systems improve through constant training. Humans must do the same - but with a twist. Unlike machines, we can learn creatively, emotionally, and socially.

  • Upskill Continuously: Stay current with emerging technologies, data literacy, and digital tools.
  • Learn How to Learn: Develop metacognitive skills - reflection, adaptability, and strategic thinking.
  • Cross-Disciplinary Thinking: Combine knowledge from science, art, philosophy, and ethics to solve complex problems.

Education must shift from static curricula to dynamic, personalized learning ecosystems. The goal isn’t just knowledge acquisition - it’s cognitive agility.

2. Cultivate Human-Centric Skills

AI excels at pattern recognition, optimization, and automation. But it lacks emotional depth, moral reasoning, and embodied experience.

Humans can thrive by honing:

  • Empathy and Emotional Intelligence: Crucial for leadership, caregiving, negotiation, and collaboration.
  • Ethical Judgment: Navigating dilemmas that algorithms can’t resolve.
  • Creativity and Imagination: Generating novel ideas, stories, and visions beyond data-driven constraints.

These aren’t just soft skills - they’re survival skills in an AI-augmented world.

3. Collaborate with AI, Not Compete

Instead of viewing AI as a rival, we should treat it as a partner. Human-AI collaboration can amplify productivity, insight, and innovation.

  • Augmented Intelligence: Use AI to enhance decision-making, not replace it.
  • Human-in-the-Loop Systems: Ensure oversight, context, and ethical checks in automated processes.
  • Co-Creation: Artists, writers, and designers can use AI as a creative tool, not a substitute.

The future belongs to those who can orchestrate symphonies between human intuition and machine precision.

4. Redefine Intelligence and Success

AI challenges our traditional notions of intelligence - memory, logic, speed. But human intelligence is multifaceted.

We must:

  • Value Diverse Intelligences: Emotional, social, spatial, and existential intelligence matter.
  • Measure Meaning, Not Just Metrics: Success isn’t just efficiency - it’s purpose, fulfillment, and impact.
  • Foster Wisdom Over Data: Wisdom integrates knowledge with experience, ethics, and foresight.

By broadening our definition of intelligence, we reclaim our relevance in a machine-dominated landscape.

5. Build Resilience - Individually and Collectively

AI’s rise brings disruption. Jobs will change, institutions will evolve, and identities may be challenged.

Humans must build:

  • Psychological Resilience: Adapt to uncertainty, ambiguity, and rapid change.
  • Social Resilience: Strengthen communities, empathy, and shared values.
  • Institutional Resilience: Reform education, governance, and labor systems to support human flourishing.

Resilience isn’t resistance - it’s transformation.

Conclusion: Evolve, Don’t Imitate

To match AI’s growth, humans must evolve - not by mimicking machines, but by deepening what makes us human. Our creativity, empathy, ethics, and adaptability are not bugs - they’re features.

The race isn’t about speed. It’s about direction. AI may be accelerating, but humans can steer. And in that steering lies our greatest power - not to outpace machines, but to outthink them.

Just try the prompt on Copilot or your favorite AI-powered assistant! Have you got a different/similar result? How big or important is the difference? Any other thoughts?
Just share the link to the post with me and I'll add it to this post as a resource!

Disclaimer: The whole text was generated by Copilot (under Windows 11) at the first attempt. This is just an experiment to evaluate feature's ability to answer standard general questions, independently on whether they are correctly or incorrectly posed. Moreover, the answers may reflect hallucinations and other types of inconsistent or incorrect reasoning.

Previous Post <<||>> Next Post

07 May 2024

🏭🗒️Microsoft Fabric: The Metrics Layer [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: 07-May-2024

The Metrics Layer in Microsoft Fabric (adapted diagram)
The Metrics Layer in Microsoft Fabric (adapted diagram)

[new feature] Metrics Layer (Metrics Store)

  • {definition}an abstraction layer available between the data store(s) and end users which allows organizations to create standardized business metrics, that are rooted in measures and are discoverable and intended for reuse
    • ⇐ {important} feature still in private preview 
  • {goal} extend existing infrastructure 
    • {benefit} leverages and extends existing features
  • {goal} provide consistent definitions and descriptions [1]
    • consistent definitions that include besides business logic additional dimensions and filters [1]
    • ⇒ {benefit} allows to standardize the metrics across the organization
    • ⇒ {benefit} enforce to enforce a SSoT
  • {goal} easy management 
    • via management views 
    • [feature] lineage 
    • [feature] source control
    • [feature] duplicate identification
    • [feature] push updates to downstream uses of the metrics 
  • {goal}searchable and discoverable metrics 
    • {feature} integration
      • based on Sempy fabric package
        • ⇐ a dataframe for storage and propagation of Power BI metadata which is part of the python-based semantic Link in Fabric
  • {goal}trust
    • [feature] trust indicators
    • {benefit} facilitates report's adoption
  • {feature} metric set 
    • {definition} a Fabric item that groups together a set of metrics into a mini-model
    • {benefit} allows to reduce the overall complexity of semantic models, while being easy to evolve and consume
    • associated with a single domain
      • ⇒ supports the data mesh architecture
    • shareable 
      • can be shared with other users
    • {action} create metric set
      • creates the actual artifact, to which metrics can be added 
  • {feature} metric
    •  {definition} a way to elevate the measures from the various semantic models existing in the organization
    • tied to the original semantic model
      • ⇒ {benefit} allows to see how a metric is used across the solutions 
    • reusable
      • can be reused in other fabric artifacts
        • new reports on the Power BI service
        • notebooks 
          • by copying the code
      • can be reused in Power BI
        • via OneLake data hub menu element
      • can be chained 
        • changes are propagated downstream 
    • materializable 
      • its output can be persisted to OneLake by saving it a delta table into a lakehouse
      • {misuse} data is persisted unnecessarily
    • {action} elevate metric
      • copies measure's definition and description
      • ⇒  implies restructuring, refactoring, moving, and testing a lot of code in the process
      • {misuse} data professionals build everything as metrics
    • {action} update metric
    • {action} add filters to metric 
    • {action} add dimensions to metric
    • {action} materialize metric 

References:
[1] Power BI Tips (2024) Explicit Measures Ep. 236: Metrics Hub, Hot New Feature with Carly Newsome (link)
[2] Power BI Tips (2024) Introducing Fabric Metrics Layer / Power Metrics Hub [with Carly Newsome] (link)

Resources:
[R1] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link]

Acronyms:
SSoT - single source of truth ()

20 April 2024

⚡️🗒️Power BI: Visual 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: 2-Nov-2026

[feature] Visual Calculations (aka Visual Calcs)

  • {def} a type of DAX calculation that's defined and executed in the scope of a visual [1] [5]
    • represent columns added to a virtual table of filtered and aggregated data [5]
      • not directly connected to the rest of the semantic model [5]
    • {characteristic} defined and evaluated only on the filtered and aggregated data that is visible in the visual 
      • the visual matrix in which the visual calculation is created [5] 
        • aka calculations table that’s defined in the DAX query [5]
      •  the filter context dictates what the measures and fields on the visual return, and the visual calculation takes those values as input for its evaluation [5]
        • ⇐ a visual calculation is only indirectly affected by filter context, not directly, the way a measure or field reference is [5]
    • {characteristic} evaluate at runtime [5]
      • like measures do [5] 
      • ⇒ they can be fully dynamic in the context of the visual [5]
    • {characteristic} a single query is sent to the source [5]
      • ⇐ no such guarantee exists for analysis that relies on measures [5] 
      • ⇐ how pronounced the effects are depends on the size of the data and the complexity of the DAX used [5]
    • {characteristic} executed on a row-by-row basis
      • like a calculated column [5]
    • {characteristic} calculated on the fly [4]
      • like a measure [5]
    • {characteristic} stored on the visual
      • ⇒ aren’t part of the semantic model [5]
      • ⇒ can refer to any data in the visual
        •  incl. columns, measures, or other visual calculations [1]
      • ⇒ anything in the model must be added to the visual before the visual calculation can refer to it [1]
      • ⇒ no need to worry about filter context [5]
      • ⇒ they can refer to the visual structure
      • ⇒ leads to more flexibility
    • {characteristic} operate on aggregated data instead of the detail level [1]
      • ⇒ leads to performance benefits [1]
  • {benefit} combine the simplicity of context from calculated columns with the on-demand calculation flexibility from measures [1]
    • ⇐ the context is 'visible
    • ⇐ share behaviors with calculated columns and measures [5]
      • ⇐ have important differences
        • ⇐ in how they can be used, where they are stored, when they are computed [5]
  • {benefit} make it easier to create calculations (that were previously hard to create) 
    • leads to simpler DAX, easier maintenance, better performance [1]
    • reuse the results from its components [2]
    • simpler than measures, more trustworthy than Excel [5]
  • {benefit} the visual context in which they operate not only describes what data is on the visual (which is then iterated over in a row context) but also the structure of the visual 
    • ⇒ a visual calculation can refer to the axes of a visual, such as the x-axis or the y-axis instead of the actual field [5]
    • ⇐ enables visual calculations to be highly flexible [5]
      • ⇐ a visual calculation can continue to work even if the field that is on the x-axis changes by simply referring to the axis instead of the actual field [5]
  • {benefit} highly visual [5] always show the data the calculation works on, and tools are provided to easily validate the results [5]
  • {benefit} should perform better than measures 
    • ⇐ because they’re executed as part of the DAX query to fetch the results instead of independently [5]
  • {limitation} unaware of the semantic model [5]
    • ⇐ as they are not part of the semantic model) [5]
    • ⇒ DAX functions that perform actions on that model don’t work (return an error) [5]
      •  e. g. RELATED, USERELATIONSHIP
  • {limitation} can’t be reused across multiple reports [5] 
    • ⇒ leads to code duplication [5]
    • ⇐ only live on the visual, which will avoid cluttering of the semantic model [5]
    • they’re saved in the metadata of the report, inside the visual definition. [5]
    • can only be used inside one report and that definitions cannot be shared across multiple reports that share the same semantic model [5]
  • (limitation) do not work together with the 'show items with no data' option, because it would result in performance issues.[5]
  • {recommendation} create measures for any key calculations that should be reused across multiple reports [5]
    •  o    allows to establish a SVoT, better reusability, maintenance, design, etc.
  • introduce a set of functions specific to visual calculations [1]
    • {category} medium-level functions
      • {function} COLLAPSE
        • the calculation is evaluated at a higher level of the axis [1]
        • navigate to a higher level in the lattice formed by the fields on the axes of the visual matrix [5] 
        • most often used for percentage of parent, grandparent, and total calculations [5]
      • {function} COLLAPSEALL
        • the calculation is evaluated at the total level of the axis [1]
        • does not take extra parameters because it always moves to the highest level on the axis [5] navigate to the highest level of the lattice on the axis specified [5]
      • {function} EXPAND
        • the calculation is evaluated at a lower level of the axis [1]
        • navigate to a lower level in the lattice formed by the fields on the axes of the visual matrix [5]
        • often used for aggregated descendant averages [5]
        • the reverse of COLLAPSE
      • {function}EXPANDALL
        • the calculation is evaluated at the leaf level of the axis [1]
        • always moves down to the lowest level (leaf level) on the axis and doesn’t take these parameters [5]
        • the reverse of COLLAPSEALL [5]
      • {function} FIRST
        • retrieves a value from the first element on a specified axis [5]
        • often used to compare against a base period or entity [5]
        • retrieves the value of the column in the first element on the specified axis since the last time the calculation was reset [5]
        • an easier-to-use shortcut to the INDEX function with the position parameter set to 1 [5]
      • {function} ISATLEVEL
        • checks whether specified columns are on the current level of the axis [5]
        • returns a Boolean (True/False) value
        • guaranteed to work correctly in visual calculations with functions that navigate the levels of the lattice [5]
        • unlike ISINSCOPE, ISFILTERED inspection functions [5]
      • {function} LAST
        • refers to the last row of an axis [1]
        • retrieves a value from the last element on a specified axis [5]
        • the reverse of FIRST
        • often used to compare against the most recent entry [5]
      • {function} LOOKUP
        • evaluates an expression with a value from a cell in the provided visual matrix using filters [5]
        • anything that is not specified is inferred from the context [5]
        • often used to compare against a specific value in the visual matrix [5]
      • {function} LOOKUPWITHTOTALS
        • uses the total for any filter that is not specified [5]
        • returns the value of the expression provided at the specified coordinates after filters have been applied [5]
          • any filter that is not specified is treated as referring to the total
          • if no single value can be determined, an error is returned [5]
          • ⇐ instead of using the context to infer any filter that is not specified like LOOKUP does [5]
        • relies on absolute navigation in the visual matrix [5]
          • ⇐ COLLAPSE and COLLAPSEALL rely on relative navigation on the lattice [5]
            • ⇐ upon case can be used to retrieve same result [5]
      • {function} NEXT
        • retrieves a value from a next element on a specified axis [5]
        • retrieves the value of the column from a next element on the specified axis since the last time the calculation was reset [5]
        • provides a shortcut to the OFFSET function with a positive value provided for the delta parameter [5]
      • {function} PREVIOUS
        • retrieves a value from a previous element on a specified axis [5]
        • retrieves the value of the column from an earlier element on the specified axis since the last time the calculation was reset [5]
        • provides a shortcut to the OFFSET function with a negative value provided for the delta parameter [5]
      • {function} RANGE
        • provides a range of rows relative from the current position on the axis [5]
        • shortcut to WINDOW 
        • returns a context 
          • ⇒ it must be used with other functions, such as CALCULATE, to actually perform a calculation [5]
        • often employed to calculate a moving sum [5]
    • {category} high-level functions
      • {function} MOVINGAVERAGE
        • adds a moving average on an axis [1]
        • involves selecting a slice of the values on an axis and returning the average over that slice [5]
        • most often used to calculate averages across periods [5]
        • provides an easier-to-use shortcut to the WINDOW function [5]
      • {function} RUNNINGSUM
        • returns the sum of all values in a column on the axis since the last time the calculation was reset, up to and including the current value [5]
          • if no reset is defined, RUNNINGSUM starts at the top of the visual matrix and continues to the end, following the sort order [5]
        • created specifically for visual calculations [5]
          • often used for Pareto analysis [5]
        • provides an easier-to-use shortcut to the WINDOW function [5]
        • it’s possible to write a running sum in a measure, though the code becomes more complex to write [5] 
          • ⇐ include explicit references to columns on which the calculation works [5]
            • ⇒ if the user changes the columns on the visual, the measure will return unexpected results and will have to be updated to reflect the changes [5]
    • {category} low-level functions
      • ⇐ {exception} are available in standard DAX
      • these functions are easier to use, though they are less flexible than their foundational counterparts [5]
      • {recommendation} rewrite visual calculation using DAX for more flexibility [5]
        • {recommendation} start with the easier-to-use visual calculations exclusive functions and resort to other functions only when needed [5]
    • {category}foundational functions
      • {parameter} relation 
        • table expression that defines from which output a value is returned, namely a table expression [5]
        • name of a table or a DAX statement that returns a table, such as ADDCOLUMNS or SUMMARIZECOLUMNS
        • any columns specified in the partitionBy parameter must come from the relation parameter or from a related table [5]
      • {parameter} orderBy
        • specifies how each partition on the relation or axis is sorted [5]
        • accepts only the ORDERBY function
        • partitions are defined by using either partitionBy or reset [5]
        • {default} ordering by every column that is in the relation or on the axis that is not specified in partitionBy or reset [5]
      • {parameter} blanks
        • specify how blank values on the axis should be ordered while the calculation traverses the axis (in a visual calculation) or the relation [5]
        • does not sort anything in the values of the visual matrix (e.g. visual calculations or measures) [5] it sorts the values of the fields on the axis used [5]
        • {value} DEFAULT
          • indicates that blank numerical values are ordered between zero and negative values. For blank textual values, the blank values are ordered before all text values, including empty text values [5]
        • {value} FIRST 
          • blank values are always ordered at the beginning, regardless of ascending or descending sorting order [5]
        • {value] LAST 
          • blank values are always ordered at the end, regardless of ascending or descending sorting order [5]
      • {parameter} partitionBy
        • specifies how the relation or axis is partitioned [5]
        • accepts the PARTITIONBY function [5]
        • {recommendation} use reset instead of partitionBy  
          • ⇐ it’s the easiest way of achieving the same result [5]
      • {parameter} matchBy
        • defines how to match data to identify unique rows [5]
        • use this parameter if you do not have anything that can uniquely identify the rows in your relation (aka composite key) [5]
        • when using axis in visual calculations, there's not need to use matchBy in visual calculations because the axis always has unique identifiers [5]
          • if no matchBy is specified and the columns in orderBy and partitionBy cannot uniquely identify every row in the relation, the foundational function will try to find the least number of additional columns required to uniquely identify the rows and append these to the orderBy value (even if you did not specify orderBy) [5] 
      • {parameter} resets
        • accepts the MATCHBY function
        • available only for visual calculations
        • the calculation is reset by dividing the data on the axis into slices, the same way partitionBy does [5]
        • {def} reset parameter
          •  defines when a calculation should restart as it traverses the visual matrix
        • {limitation} one cannot specify both reset and partitionBy
          • one can think of the reset parameter as being mapped to the partitionBy parameter, however reset automatically includes parent levels and partitionBy does not [5]
      • {function} INDEX
        • returns a row in an absolute position
        • position parameter defines the absolute position on the relation or axis from which to obtain the data.
      • {function} OFFSET
        • returns a row in a relative position
        • {parameter} delta
          • specifies the relative position on the relation or axis from which to obtain the data [5]
          • when specifying a delta that causes a relative movement that does not exist on the partition, when specifying  0 or BLANK(), then OFFSET will not perform a relative movement, and the context is set to the current row [5]
          • any DAX expression that returns a scalar value is valid [5]
      • {function} RANK
        • provides a ranking of each row within a partition, sorted by the specified sort order [5]
        • returns a blank value for total rows when used in measures but returns a value on those rows when used in visual calculations [5]
        • expects a table and applies an expression to rank the rows in the table [5]
        • {warning} it's not the column-based version of RANKX [5]
        • {parameter} ties parameter
          • {value|default} SKIP
            • if two rows end up with the same rank, they will both be assigned the same rank and the next rank number will be skipped [5]
          • {value}DENSE
            • the next rank number will not be skipped [5]
      • {function} ROWNUMBER
        • returns a unique ranking of each row within a partition, sorted by the specified sort order [5]
        • returns an error if it cannot uniquely identify each row [5]
          • ⇒ guarantees that the same number will never be assigned twice [5]
      • {function}WINDOW
        • returns multiple rows, which are positioned at a selectable absolute or relative interval [5]
        • {parameter} from
          • indicate where the window start
        • {parameter} to
          • indicate where the window ends
        • {parameter} from_type
          • specifies whether the window starts at either 
          • {default} relative position (REL)
            • then a negative value provided for this parameter specifies the number of rows to go back from the current position to get the first (or last) row in the window [5]
          • absolute position (ABS) [4]
            • indicate the 1-based absolute position in the current partition of the start and end of the window [4]
            • 1= first row, -1 = last row
        • {parameter} to_type
          • see from_type
      • {category} supportive functions
        • used as inputs to specific foundational function parameters with the same name [5]
        • on their own, these functions provide no value [5]
        • {recommendation) specify an axis value for the relation parameter and use reset as needed when using foundational functions in visual calculations [5]
          • the Axis parameter 
            •  defines the path or direction through the visual matrix that a calculation follows, controlling how functions move through the data displayed in a visual
              • tells PBI how to traverse the visual's data when performing the calculation, giving precise control on how the calculations are performed
      • {function} ORDERBY
        • define the sorting order within each partition of the relation or axis on which the function operates [5]
      • {function} PARTITIONBY
        • indicates if and how to slice up the data in the relation or axis on which the foundational function operates [5]
        • it can be skept if no slices are defined [5]
      • {function} MATCHBY
        • instruct DAX on how to determine the current row [5]
  • {category} shared functions
    • DAX functions which are shared across all experiences [5]
  • {category} exclusive functions 
    • functions introduced in DAX solely for visual calculations [5]
  • {category} blocked functions 
    • functions that reach out to the model [5]
  • {default} most of them are evaluated row-by-row [1]
    • ⇐ like a calculated column
    • there's no need to add an aggregation function [1]
      •  it's better not to add such aggregates when they're not necessary [1]
  • {operation} create calculation
    • adds the visual calculation to the visual
    • it's possible to create visual calculations directly in the service [5]
    • allows to create very complex visual calculations in steps and hide any irrelevant intermediate results [5]
    • creation is not traced as activity in audit logs but is covered in a generic activity named Update Report Content [5]
  • {operation} hide calculation
    • calculations that aren't needed in the visual can be hidden [2]
    • hidden fields enable users to hide elements from the visual [5]
    • {def} hidden field
      • a column in a visual intended solely to support a visual calculation, but is not displayed to the report consumer
  • {operation} copy calculation
    • copies the calculation between visuals and if intermediary steps are not there, they will be copied as well [planned] [2]
  • {operation} formatting 
    • ⇐ do not take on the format of any measures used to create them [5]
  • {operation} view a visual as a visual matrix 
    • enables users to add more calculations, which can be seen as new columns in the visual matrix [5]
  • {feature} templates 
    • ready available calculation constructs 
    • {benefit} make it easier to write common calculations [1]
  • {feature|planned} support for Scanner API [2]
  • {feature} explore
    • new experience that allows to explore data in a focused way [4]
    • allows adding visual calculations to visuals [4]
  • {feature} parameter pickers
    • allows to create visual calculations faster by picking parameters [3]
    • {limitation} only available for required parameters on functions that are exclusive to visual calculations (and select other functions) that have a defined list of options [3]
      •  required parameters that can take any text, or numerical value will not get a parameter picker, and neither will many DAX functions [3]
  • {feature} visual preview 
    • shows users what the visual will look like when leaving the visual calculations edit mode and returning to the report [5]
    • allows users to see what impact newly added visual calculations have on the visual, and how the visual will look like if certain measures or visual calculations are hidden [5]
  • {feature} visual matrix
    • {def} the internal tabular representation of the data shown in the visual that PBI uses to evaluate the calculation
    • the data representation of your visual 
      • ⇐ shows you the outcomes of all newly added calculations [5] 
      • ⇐ the simplest representation of the data used to create the visual [5]
    • offers a way to structure data dynamically based on rows and columns in a WYSIWYG fashion [5]
    • doesn't display any formatting that may be applied to the visual itself [5]
    • every value in a visual calculation must exist in the visual matrix
      • ⇒ both the original value residing in the model and the visual calculation will be shown in the resulting visual [5]
  • {feature} formula bar 
  • allows to write and edit visual calculations [5]
  • {parameter} axis 
    • influences how the visual calculation traverses the visual matrix [1] 
      • ⇐ defines the direction in which the running sum should be calculate [5]
    • can be seen as the axis of a chart, which has an x-axis and a y-axis [5]
    • not available in measures, calculated columns, or calculated tables [5]
    • defines the direction in which the running sum should be calculated: over rows, columns, or a combination [5]
    • {default} set to the first axis in the visual
    • {value} ROWS
      • the visual calculation is evaluated row-by-row in the visual matrix, from top to bottom. [1]
    • {value} COLUMNS
      • the visual calculation is evaluated row-by-row in the visual matrix, from left to right [1]
    • {value} ROWS COLUMNS
      • calculates vertically across rows from top to bottom, continuing column by column from left to right [1]
    • {value} COLUMNS ROWS
      • calculates horizontally across columns from left to right, continuing row by row from top to bottom [1]
    • {warning} not all visuals provide all axes, and some visuals provide no axes [1]
      • references to a non-existent or invalid axis is permissible and will be ignored [3]
  • {parameter} reset 
    • influences if and when the function resets its value to 0 or switches to a different scope while traversing the visual matrix [1]
    • expects there to be multiple levels on the axis [1]
      • ⇐ use PARTITIONBY if there's only one level on the axis [1]
    • {value|default} NONE
      • means the visual calculation is never restarted [1]
    • {value} HIGHESTPARENT 
      • resets the calculation when the value of the highest parent on the axis changes [1]
    • {value} LOWESTPARENT 
      • resets the calculations when the value of the lowest parent on the axis changes [1]
    • {value} numerical value
      • refers to the fields on the axis, with the highest field being one [1]
  • {concept} lattice
    • formed by all the fields on all the axes [5]
    • visual calculations calculate results on various levels on the lattice [5]
    • lattice navigation functions allow users to explicitly move around in the lattice [5]
      • e.g. COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL
    • {default} the data type of a visual calculation is decimal number [5]
  • {concept} format strings
    • allow for a more fine-grained level of formatting
  • {feature} traceability 
    • prevents redundancy and conflicting definitions by identifying inconsistent calculations [5] 
    • using audit trails and versioning systems further enhances accountability and enables swift corrections when needed [5]
  • {limitation} functions that rely on model relationships  aren't available
    • e.g. USERELATIONSHIP, RELATED or RELATEDTABLE
  • {limitation} not all visual types are supported [1]
    • ⇐ for the full list of limitations see [1]
  • {limitation} one can't filter on visual calculations [1]
  • {limitation} underlying data can't be exported [1]
  • {limitation} don't support conditional formatting
  • {concept} skippable parameters
    • introduced with visual calculations [5]
    • allow for cleaner code because users can simply omit any unnecessary optional parameters [5]
      • ⇐ unlike with DAX functions that do not support skippable parameters [5]
  • {concept} telemetry 
    • the collection and analysis of data to monitor, measure, and optimize system performance[5]
    • can provide valuable insights into the usage patterns, dependencies, and performance of visual calculations [5]
    • allows administrators can identify discrepancies and ensure that visual calculations align with organizational standards [5]
  •  

References: 
[1] Microsoft Learn (2024) Power BI: Using visual calculations [preview] [link]
[2] SSBI Central (2024) Visual Calculations - Making DAX easier, with Jeroen ter Heerdt [link]
[3] Microsoft Power BI Updates (2025) Power BI June 2025 Feature Summary [link]
[4] Microsoft Learn (2025) Power BI: Use Explore (preview) in the Power BI service [link]
[5] Jeroen ter Heerdt et al (2026) Microsoft Power BI Visual Calculations: Simplifying DAX 

02 December 2015

🪙Business Intelligence: Measure (Just the Quotes)

"[…] when you can measure what you are speaking about, and express it in numbers, you know something about it; but when you cannot express it in numbers, your knowledge is of a meager and unsatisfactory kind; it may be the beginning of knowledge, but you have scarcely in your thoughts advanced to the state of science." (William T Kelvin, "Electrical Units of Measurement", 1883)

"Quantify. If whatever it is you’re explaining has some measure, some numerical quantity attached to it, you’ll be much better able to discriminate among competing hypotheses. What is vague and qualitative is open to many explanations." (Carl Sagan, "The Demon-Haunted World: Science as a Candle in the Dark", 1995)

"Clearly, the mean is greatly influenced by extreme values, but it can be appropriate for many situations where extreme values do not arise. To avoid misuse, it is essential to know which summary measure best reflects the data and to use it carefully. Understanding the situation is necessary for making the right choice. Know the subject!" (Herbert F Spirer et al, "Misused Statistics" 2nd Ed, 1998)

"Balanced Scorecard theory is flawed because it presents managers with a scorecard which gives no score – that is no single-valued measure how they have performed. Thus managers evaluated with such a system […] have no way to make principled or purposeful decisions." (Michael Jensen,"Value Maximization, Stakeholder Theory, and the Corporate Objective Function", Journal of Applied Corporate Finance, 2001)

"Changing measures are a particularly common problem with comparisons over time, but measures also can cause problems of their own. [...] We cannot talk about change without making comparisons over time. We cannot avoid such comparisons, nor should we want to. However, there are several basic problems that can affect statistics about change. It is important to consider the problems posed by changing - and sometimes unchanging - measures, and it is also important to recognize the limits of predictions. Claims about change deserve critical inspection; we need to ask ourselves whether apples are being compared to apples - or to very different objects." (Joel Best, "Damned Lies and Statistics: Untangling Numbers from the Media, Politicians, and Activists", 2001)

"Our culture, obsessed with numbers, has given us the idea that what we can measure is more important than what we can't measure. Think about that for a minute. It means that we make quantity more important than quality." (Donella Meadows, "Thinking in Systems: A Primer", 2008)

"What gets measured gets managed - even when it’s pointless to measure and manage it, and even if it harms the purpose of the organisation to do so." (Simon Caulkin, "The rule is simple: be careful what you measure", 2008) [source]

"What gets measured gets managed - so be sure you have the right measures, because the wrong ones kill." (Simon Caulkin, "The rule is simple: be careful what you measure", 2008) [source]

"Once these different measures of performance are consolidated into a single number, that statistic can be used to make comparisons […] The advantage of any index is that it consolidates lots of complex information into a single number. We can then rank things that otherwise defy simple comparison […] Any index is highly sensitive to the descriptive statistics that are cobbled together to build it, and to the weight given to each of those components. As a result, indices range from useful but imperfect tools to complete charades." (Charles Wheelan, "Naked Statistics: Stripping the Dread from the Data", 2012)

"Until a new metric generates a body of data, we cannot test its usefulness. Lots of novel measures hold promise only on paper." (Kaiser Fung, "Numbersense: How To Use Big Data To Your Advantage", 2013)

"Selecting the right measure and measuring things right are both art and science. And KPIs influence management behavior as well as business culture." (Pearl Zhu, "CIO Master: Unleash the Digital Potential of It", 2016)

"It’d be nice to fondly imagine that high-quality statistics simply appear in a spreadsheet somewhere, divine providence from the numerical heavens. Yet any dataset begins with somebody deciding to collect the numbers. What numbers are and aren’t collected, what is and isn’t measured, and who is included or excluded are the result of all-too-human assumptions, preconceptions, and oversights." (Tim Harford, "The Data Detective: Ten easy rules to make sense of statistics", 2020)

"People do care about how they are measured. What can we do about this? If you are in the position to measure something, think about whether measuring it will change people’s behaviors in ways that undermine the value of your results. If you are looking at quantitative indicators that others have compiled, ask yourself: Are these numbers measuring what they are intended to measure? Or are people gaming the system and rendering this measure useless?" (Carl T Bergstrom & Jevin D West, "Calling Bullshit: The Art of Skepticism in a Data-Driven World", 2020)

"As long as measurements are abused as a tool of control, measuring will remain the weakest area in a manager’s performance." (Peter Drucker)

"For although it is certainly true that quantitative measurements are of great importance, it is a grave error to suppose that the whole of experimental physics can be brought under this heading. We can start measuring only when we know what to measure: qualitative observation has to precede quantitative measurement, and by making experimental arrangements for quantitative measurements we may even eliminate the possibility of new phenomena appearing." (Heinrich B G Casimir)

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.