⚡️🗒️Power BI: CALCULATE [Notes]

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

Last updated: 31-Oct-2026

[DAX] CALCULATE

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

References:

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

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

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

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

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

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

 

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

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

Last updated: 28-Oct-2026

[feature] DAX Calculations

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

References:

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

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

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

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

 

⚡️🗒️Power BI: Context [Notes]

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

Last updated: 28-Oct-2026

[DAX] Context

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

References:

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

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

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

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

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

 

Resources:  

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

 

Acronyms:
DAX - Data Analysis eXpressions

RLS - Row Level Security

 

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

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

Last updated: 13-Oct-2026

[DAX] ALLSELECTED()

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

Previous Post <<||>> Next Post

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

📊Graphical Representation: Graphics We Live By (Part VIII: List of Items in Power BI)

Graphical Representation Series

Introduction

There are situations in which one needs to visualize only the rating, other values, or ranking of a list of items (e.g. shopping cart, survey items) on a scale (e.g. 1 to 100, 1 to 10) for a given dimension (e.g. country, department). Besides tables, in Power BI there are 3 main visuals that can be used for this purpose: the clustered bar chart, the line chart (aka line graph), respectively the slopegraph:

Main Display Methods

Main Display Methods

For a small list of items and dimension values probably the best choice would be to use a clustered bar chart (see A). If the chart is big enough, one can display also the values as above. However, the more items in the list, respectively values in the dimension, the more space is needed. One can maybe focus then only on a subset of items from the list (e.g. by grouping several items under a category), respectively choose which dimension values to consider. Another important downside of this method is that one needs to remember the color encodings. 

This downside applies also to the next method - the use of a line chart (see B) with categorical data, however applying labels to each line simplifies its navigation and decoding. With line charts the audience can directly see the order of the items, the local and general trends. Moreover, a line chart can better scale with the number of items and dimension values.

The third option (see C), the slopegraph, looks like a line chart though it focuses only on two dimension values (points) and categorizes the line as "down" (downward slope), "neutral" (no change) and "up" (upward slope). For this purpose, one can use parameters fields with measures. Unfortunately, the slopegraph implementation is pretty basic and the labels overlap which makes the graph more difficult to read. Probably, with the new set of changes planned by Microsoft, the use of conditional formatting of lines would allow to implement slope graphs with line charts, creating thus a mix between (B) and (C).

This is one of the cases in which the Y-axis (see B and C) could be broken and start with the meaningful values. 

Table Based Displays

Especially when combined with color encodings (see C & G) to create heatmap-like displays or sparklines (see E), tables can provide an alternative navigation of the same data. The color encodings allow to identify the areas of focus (low, average, or high values), while the sparklines allow to show inline the trends. Ideally, it should be possible to combine the two displays.  

Table Displays and the Aster Plot

One can vary the use of tables. For example, one can display only the deviations from one of the data series (see F), where the values for the other countries are based on AUS. In (G), with the help of visual calculations one can also display values' ranking. 

Pie Charts

Pie charts and their variations appear nowadays almost everywhere. The Aster plot is a variation of the pie charts in which the values are encoded in the height of the pieces. This method was considered because the data used above were encoded in 4 similar plots. Unfortunately, the settings available in Power BI are quite basic - it's not possible to use gradient colors or link the labels as below:

Source Data as Aster Plots

Sankey Diagram

A Sankey diagram is a data visualization method that emphasizes the flow or change from one state (the source) to another (the destination). In theory it could be used to map the items to the dimensions and encode the values in the width of the lines (see I). Unfortunately, the diagram becomes challenging to read because all the lines and most of the labels intersect. Probably this could be solved with more flexible formatting and a rework of the algorithm used for the display of the labels (e.g. align the labels for AUS to the left, while the ones for CAN to the right).

Sankey Diagram

Data Preparation

A variation of the above image with the Aster Plots which contains only the plots was used in ChatGPT to generate the basis data as a table via the following prompts:

• retrieve the labels from the four charts by country and value in a table
• consolidate the values in a matrix table by label country and value

The first step generated 4 tables, which were consolidated in a matrix table in the second step. Frankly, the data generated in the first step should have been enough because using the matrix table required an additional step in DAX.

Here is the data imported in Power BI as the Industries query:

let
    Source = #table({"Label","Australia","Canada","U.S.","Japan"}
, {
 {"Credit card","67","64","66","68"}
, {"Online retail","55","57","48","53"}
, {"Banking","58","53","57","48"}
, {"Mobile phone","62","55","44","48"}
, {"Social media","74","72","62","47"}
, {"Search engine","66","64","56","42"}
, {"Government","52","52","58","39"}
, {"Health insurance","44","48","50","36"}
, {"Media","52","50","39","23"}
, {"Retail store","44","40","33","23"}
, {"Car manufacturing","29","29","26","20"}
, {"Airline/hotel","35","37","29","16"}
, {"Branded manufacturing","36","33","25","16"}
, {"Loyalty program","45","41","32","12"}
, {"Cable","40","39","29","9"}
}
),
    #"Changed Types" = Table.TransformColumnTypes(Source,{{"Australia", Int64.Type}, {"Canada", Int64.Type}, {"U.S.", Number.Type}, {"Japan", Number.Type}})
in
    #"Changed Types"

Transforming (unpivoting) the matrix to a table with the values by country:

IndustriesT = UNION (
    SUMMARIZECOLUMNS(
     Industries[Label]
     , Industries[Australia]
     , "Country", "Australia"
    )
    , SUMMARIZECOLUMNS(
     Industries[Label]
     , Industries[Canada]
     , "Country", "Canada"
    )
    , SUMMARIZECOLUMNS(
     Industries[Label]
     , Industries[U.S.]
     , "Country", "U.S."
    )
    ,  SUMMARIZECOLUMNS(
     Industries[Label]
     , Industries[Japan]
     , "Country", "Japan"
    )
)

Notes:

The slopechart from MAQ Software requires several R language libraries to be installed (see how to install the R language and optionally the RStudio). Run the following scripts, then reopen Power BI Desktop and enable running visual's scripts.

install.packages("XML")
install.packages("htmlwidgets")
install.packages("ggplot2")
install.packages("plotly")

Happy (de)coding!

Previous Post <<||>> Next Post

⚡️Power BI: Working with Visual Calculations (Part III: Matrix Tables with Square Numbers as Example)

Introduction

In the previous post I exemplified various operations that can be performed with visual calculations on simple tables based on square numbers. Changing the simple table to a matrix table doesn't bring any benefit. The real benefit comes when one restructures the table to store only a cell per row in a table. 

Data Modelling

For this the Magic5 table can be transformed via the following code, which creates a second table (e.g. M5):

M5 = UNION (
    SUMMARIZECOLUMNS(
     Magic5[Id]
     , Magic5[R]
     , Magic5[Index]
     , Magic5[C1]
     , "Col", "C1"
    )
    , SUMMARIZECOLUMNS(
     Magic5[Id]
     , Magic5[R]
     , Magic5[Index]
     , Magic5[C2]
     , "Col", "C2"
    )
    , SUMMARIZECOLUMNS(
     Magic5[Id]
     , Magic5[R]
     , Magic5[Index]
     , Magic5[C3]
     , "Col", "C3"
    )
    ,  SUMMARIZECOLUMNS(
     Magic5[Id]
     , Magic5[R]
     , Magic5[Index]
     , Magic5[C4]
     , "Col", "C4"
    )
    , SUMMARIZECOLUMNS(
      Magic5[Id]
     , Magic5[R]
     , Magic5[Index]
     , Magic5[C5]
     , "Col", "C5"
    )
)

Once this done, one can add the column [Col] as values for the matrix in a new visual. From now on, all the calculations can be done on copies of this visual. 

Simple Operations

The behavior of the RUNNINGSUM and other functions is different when applied on a matrix table because the formula is applied to every cell of the N*N table, a column with the result being added for each existing column of the matrix.

Moreover, there are four different ways of applying the formula based on the Axis used. ROW calculates the formula by the row within a column:

Run SumByRow(C) = RUNNINGSUM([C], ROWS)

Output:

R	C	Run Sum(C)	C	Run Sum(C)	C	Run Sum(C)	C	Run Sum(C)	C	Run Sum(C)
R1	18	18	25	25	2	2	9	9	11	11
R2	4	22	6	31	13	15	20	29	22	33
R3	15	37	17	48	24	39	1	30	8	41
R4	21	58	3	51	10	49	12	42	19	60
R5	7	65	14	65	16	65	23	65	5	65

By providing COLUMNS as parameter for the Axis makes the calculation run by the column within a row: 

Run SumByCol(C) = RUNNINGSUM([C], COLUMNS)

Output:

R	C	Run Sum(C)	C	Run Sum(C)	C	Run Sum(C)	C	Run Sum(C)	C	Run Sum(C)
R1	18	18	25	43	2	45	9	54	11	65
R2	4	4	6	10	13	23	20	43	22	65
R3	15	15	17	32	24	56	1	57	8	65
R4	21	21	3	24	10	34	12	46	19	65
R5	7	7	14	21	16	37	23	60	5	65

By providing ROW COLUMNS as parameter for the Axis makes the calculation run by the column and then continuing the next column (without resetting the value at the end of the column):

Run SumByRow-Col(C) = RUNNINGSUM([C],ROWS COLUMNS)

Output:

R	C	Run Sum(C)	C	Run Sum(C)	C	Run Sum(C)	C	Run Sum(C)	C	Run Sum(C)
R1	18	18	25	90	2	132	9	204	11	271
R2	4	22	6	96	13	145	20	224	22	293
R3	15	37	17	113	24	169	1	225	8	301
R4	21	58	3	116	10	179	12	237	19	320
R5	7	65	14	130	16	195	23	260	5	325

By providing COLUMNS ROWS as parameter for the Axis makes the calculation run by the row and then continuing the next row (without resetting the value at the end of the column):

Run SumByCol-Row = RUNNINGSUM([C],COLUMNS ROWS)

Output:

R	C	Run Sum(C)	C	Run Sum(C)	C	Run Sum(C)	C	Run Sum(C)	C	Run Sum(C)
R1	18	18	25	43	2	45	9	54	11	65
R2	4	69	6	75	13	88	20	108	22	130
R3	15	145	17	162	24	186	1	187	8	195
R4	21	216	3	219	10	229	12	241	19	260
R5	7	267	14	281	16	297	23	320	5	325

Ranking

RANK can be applied independent of the values, or considering the value with ASC or DESC sorting:

RankByRow = RANK(DENSE,ROWS) -- ranking by row independent of values
RankByRow ASC = RANK(DENSE,ROWS, ORDERBY([C],ASC)) -- ranking by row ascending
RankByRow DESC = RANK(DENSE,ROWS, ORDERBY([C], DESC)) -- ranking by row descending
RankByRow-Col ASC = RANK(DENSE,ROWS COLUMNS, ORDERBY([C],ASC)) -- ranking by row columns ascending
RankByRow-Col DESC = RANK(DENSE,ROWS COLUMNS, ORDERBY([C], DESC)) -- ranking by row columns ascending

[RankByRow-Col ASC] matches the actual numbers from the matrix and is thus useful when sorting any numbers accordingly. 

Differences

Differences can be calculated between any of the cells of the matrix:

DiffToPrevByRow = [C] - PREVIOUS([C])  -- difference to previous record
DiffToPrevByRow* = IF(NOT(IsBlank(PREVIOUS([C]))), [C] - PREVIOUS([C])) -- extended difference to previous record
DiffToPrevByRow-Col = [C] - PREVIOUS([C],, ROWS COLUMNS) -- difference to previous record by ROWS COLUMNS
DiffToFirstByRow = [C] - FIRST([C]) -- difference to first record
DiffToPrevByCol = [C] - FIRST([C], COLUMNS) -- difference to previous record COLUMNS

Ranking = RANK(DENSE, ROWS COLUMNS, ORDERBY([C], ASC)) -- ranking of values by ROWS COLUMNS
OffsetDiffToPrevByRow = [C] - calculate([C], OFFSET(1, ROWS, ORDERBY([Ranking],DESC))) -- difference to the previous record by ROW
OffsetDiffToPrevByRow-Col = [C] - calculate([C], OFFSET(1, ROWS COLUMNS, ORDERBY([Ranking],DESC))) -- difference to the previous record by ROW

Ranking has been introduced to facilitate the calculations based on OFFSET.

The other functions [1] can be applied similarly.

Happy coding!

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) Power BI: Using visual calculations [preview] (link)

⚡️Power BI: Working with Visual Calculations (Part II: Simple Tables with Square Numbers as Example) 🆕

Introduction

The records behind a visual can be mentally represented as a matrix, the visual calculations allowing to tap into this structure intuitively and simplify many of the visualizations used. After a general test drive of the functionality, it makes sense to dive deeper into the topic to understand more about the limitations, functions behavior and what it takes to fill the gaps. This post focuses on simple tables, following in a next post to focus on matrices and a few other topics. 

For exemplification, it makes sense to use a simple set of small numbers that are easy to work with, and magic squares seem to match this profile. A magic square is a matrix of positive sequential numbers in which each row, each column, and both main diagonals are the same [1]. Thus, a square of order N has N*N numbers from 1 to N*N, the non-trivial case being order 3. However, from the case of non-trivial squares, the one of order 5 provides a low order and allows hopefully the minimum needed for exemplification:

18	25	2	9	11
4	6	13	20	22
15	17	24	1	8
21	3	10	12	19
7	14	16	23	5
1	7	13	19	25

Data Modeling

One magic square should be enough to exemplify the various operations, though for testing purposes it makes sense to have a few more squares readily available. Each square has an [Id], [C1] to [C5] corresponds to matrix's columns, while [R] stores a row identifier which allows to sort the values the way they are stored in the matrix:

let
    Source = #table({"Id","C1","C2","C3","C4","C5","R"}
, {
{1,18,25,2,9,11,"R1"},
{1,4,6,13,20,22,"R2"},
{1,15,17,24,1,8,"R3"},
{1,21,3,10,12,19,"R4"},
{1,7,14,16,23,5,"R5"},
{2,1,7,13,19,25,"R1"},
{2,14,20,21,2,5,"R2"},
{2,22,3,9,15,16,"R3"},
{2,10,11,17,23,4,"R4"},
{2,18,24,5,6,12,"R5"},
{3,1,2,22,25,15,"R1"},
{3,9,10,16,11,19,"R2"},
{3,17,23,13,5,7,"R3"},
{3,24,12,6,20,3,"R4"},
{3,14,18,8,4,21,"R5"},
{4,22,6,3,18,16,"R1"},
{4,4,14,11,15,21,"R2"},
{4,5,8,12,23,17,"R3"},
{4,25,13,19,7,1,"R4"},
{4,9,24,20,2,10,"R5"},
{5,5,9,20,25,6,"R1"},
{5,13,15,2,11,24,"R2"},
{5,21,1,23,3,17,"R3"},
{5,19,18,4,14,10,"R4"},
{5,7,22,16,12,8,"R5"}
}
),
    #"Changed Type to Number" = Table.TransformColumnTypes(Source,{{"C1", Int64.Type}, {"C2", Int64.Type}, {"C3", Int64.Type}, {"C4", Int64.Type}, {"C5", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type to Number",{{"Id", Order.Ascending}, {"R", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

The column names and the row identifier could have been numeric values from 1 to 5, though it could have been confounded with the actual numeric values.

In addition, the columns [C1] to [C5] were formatted as integers and an index was added after sorting the values after [Id] and [R]. Copy the above code as a Blank Query in Power BI and change the name to Magic5. 

Prerequisites

For the further steps you'll need to enable visual calculations in Power BI Developer via:
File >> Options and settings >> Options >> Preview features >> Visual calculations >> (check)

Into a Table visual drag and drop [R], [C1] to [C5] as column and make sure that the records are sorted ascending by [R]. To select only a square, add a filter based on the [Id] and select the first square. Use further copies of this visual for further tests. 

Some basic notions of Algebra are recommended but not a must. If you worked with formulas in Excel, then you are set to go. 

In Mathematics a matrix starts from the top left side and one moves on the rows (e.g. 18, 25, 2, ...) and then on the columns. With a few exceptions in which the reference is based on the latest value from a series (see Exchange rates), this is the direction that will be followed. 

Basic Operations

Same as in Excel [C1] + [C2] creates a third column in the matrix that stores the sum of the two. The sum can be further applies to all the columns:

Sum(C) = [C1] + [C2] + [C3] + [C4] + [C5] -- sum of all columns (should amount to 65)

The column can be called "Sum", "Sum(C)" or any other allowed unique name, though the names should be meaningful, useful, and succinct, when possible.

Similarly, one can work with constants, linear or nonlinear transformations (each formula is a distinct calculation):

constant = 1 -- constant value
linear = 2*[C1] + 1 -- linear translation: 2*x+1

linear2 = 2*[C1] + [constant] -- linear translation: 2*x+1
quadratic = Power([C1],2) + 2*[C1] + 1 -- quadratic translation: x^2+2*x+1
quadratic2 = Power([C1],2) + [linear] -- quadratic translation: x^2+2*x+1

Output:

R	C1	constant	linear	linear2	quadratic	quadratic2
R1	18	1	37	37	361	361
R2	4	1	9	9	25	25
R3	15	1	31	31	256	256
R4	21	1	43	43	484	484
R5	7	1	15	15	64	64

Please note that the output was duplicated in Excel (instead of making screenshots).

Similarly, can be build any type of formulas based on one or more columns.

With a simple trick, one can use DAX functions like SUMX, PRODUCTX, MINX or MAXX as well:

Sum2(C) = SUMX({[C1], [C2], [C3], [C4], [C5]}, [Value]) -- sum of all columns

Prod(C) = PRODUCTX({[C1], [C2], [C3], [C4], [C5]}, [Value]) -- product of all columns

Avg(C) = AVERAGEX({[C1], [C2], [C3], [C4], [C5]}, [Value]) -- average of all columns

Min(C) = MINX({[C1], [C2], [C3], [C4], [C5]}, [Value]) -- minimum value of all columns

Max(C) = MAXX({[C1], [C2], [C3], [C4], [C5]}, [Value]) -- maximum value of all columns
Count(C) = COUNTX({[C1], [C2], [C3], [C4], [C5]},[Value]) -- counts the number of columns

Output:

C1	C2	C3	C4	C5	Sum(C)	Avg(C)	Prod(C)	Min(C)	Max(C)	Count(C)
18	25	2	9	11	65	13	89100	2	25	5
4	6	13	20	22	65	13	137280	4	22	5
15	17	24	1	8	65	13	48960	1	24	5
21	3	10	12	19	65	13	143640	3	21	5
7	14	16	23	5	65	13	180320	5	23	5

Unfortunately, currently there seems to be no way available for applying such calculations without referencing the individual columns. 

Working across Rows

ROWNUMBER and RANK allow to rank a cell within a column independently, respectively dependently of its value:

Ranking = ROWNUMBER() -- returns the rank in the column (independently of the value)
RankA(C) = RANK(DENSE, ORDERBY([C1], ASC)) -- ranking of the value (ascending) 

RankD(C) = RANK(DENSE, ORDERBY([C1], DESC)) -- ranking of the value (descending) 

Output:

R	C1	Ranking	RankA(C)	RankD(C)
R1	18	1	4	2
R2	4	2	1	5
R3	15	3	3	3
R4	21	4	5	1
R5	7	5	2	4

PREVIOUS, NEXT, LAST and FIRST allow to refer to the values of other cells within the same column:

Prev(C) = PREVIOUS([C1]) -- previous cell
Next(C) = NEXT([C1])  -- next cell

First(C) = FIRST([C1]) -- first cell
Last(C) = LAST([C1]) -- last cell

Output:

R	C1	Prev(C)	NextC)	First(C)	Last(C)
R1	18		4	18	7
R2	4	18	15	18	7
R3	15	4	21	18	7
R4	21	15	7	18	7
R5	7	21		18	7

OFFSET is a generalization of these functions

offset(2) = calculate([C1], offset(2)) -- 
offset(-2) = calculate([C1], offset(-2))
Ind = ROWNUMBER() -- index
inverse = calculate([C1], offset(6-2*[Ind])) -- inversing the values based on index

Output:

R	C1	offset(2)	offset(-2)	ind	inverse
R1	18	15		1	7
R2	4	21		2	21
R3	15	7	18	3	15
R4	21		4	4	4
R5	7		15	5	18

The same functions allow to calculate the differences for consecutive values:

DiffToPrev(C) = [C1] - PREVIOUS([C1]) -- difference to previous 
DiffToNext(C) = [C1] - PREVIOUS([C1]) -- difference to next 
DiffTtoFirst(C) = [C1] - FIRST([C1]) -- difference to first
DiffToLast(C) = [C1] - LAST([C1]) -- difference to last

Output:

R	C1	DiffToPrev(C)	DiffToNextC)	DiffToFirst(C)	DiffToLast(C)
R1	18	18	14	0	11
R2	4	-14	-11	-14	-3
R3	15	11	-6	-3	8
R4	21	6	14	3	14
R5	7	-14	7	-11	0

DAX makes available several functions for working across the rows of the same column. Two of the useful functions are RUNNINGSUM and MOVINGAVERAGE:

Run Sum(C) = RUNNINGSUM([C1]) -- running sum

Moving Avg3(C) = MOVINGAVERAGE([C1], 3) -- moving average for the past 3 values

Moving Avg2(C) = MOVINGAVERAGE([C1], 2) -- moving average for the past 2 values

Unfortunately, one can use only the default sorting of the table with the functions that don't support the ORDERBY parameter. Therefore, when the table needs to be sorted descending and the RUNNINGSUM calculated ascending, for the moment there's no solution to achieve this behavior. However, it appears that Microsoft is planning to implement a solution for this issue.

RUNNINGSUM together with ROWNUMBER can be used to calculate a running average:

Run Avg(C) = DIVIDE(RUNNINGSUM([C1]), ROWNUMBER()) -- running average

Output:

R	C1	Run Sum(C)	Moving Avg3(C)	Moving Avg2(C)	Run Avg(C)
R1	18	18	18	18	18
R2	4	22	11	11	11
R3	15	37	12.33	9.5	12.33
R4	21	58	13.33	18	14.5
R5	7	65	14.33	14	13

With a mathematical trick that allows to transform a product into a sum of elements by applying the Exp (exponential) and Log (logarithm) functions (see the solution in SQL), one can run the PRODUCT across rows, though the values must be small enough to allow their multiplication without running into issues:

Ln(C) = IFERROR(LN([C1]), Blank()) -- applying the natural logarithm
Sum(Ln(C)) = RUNNINGSUM([Ln(C)]) -- running sum
Run Prod(C) = IF(NOT(ISBLANK([Sum(Ln(C))])), Exp([Sum(Ln(C))])) -- product across rows

Output:

R	C1	Ln(C)	Sum(Ln(C))	Run Prod(C)
R1	18	2.89	2.89	18
R2	4	1.39	4.28	72
R3	15	2.71	6.98	1080
R4	21	3.04	10.03	22680
R5	7	1.95	11.98	158760

These three calculations could be brought into a single formula, though the result could be more difficult to troubleshoot. The test via IsBlank is necessary because otherwise the exponential for the total raises an error. 

Considering that when traversing a column it's enough to remember the previous value, one can build MIN and MAX functionality across a column: 

Run Min = IF(OR(Previous([C1]) > [C1], IsBlank(Previous([C1]))), [C1], Previous([C1])) -- minimum value across rows
Run Max = IF(OR(Previous([C1]) < [C1], IsBlank(Previous([C1]))), [C1], Previous([C1])) -- maximum across rows

Happy coding!

Previous Post <<||>> Next Post

References:
[1] Wikipedia (2024) Magic Squares (online)
[2] Microsoft Learn (2024) Power BI: Using visual calculations [preview] (link)