Disclaimer: This is work in progress intended to consolidate information from
various sources.
Last updated: 20-Apr-2024
[feature] Visual Calculations (aka Visual Calcs)
- {definition} a type of DAX calculation that's defined and executed directly on a visual [1]
- {benefit} make it easier to create calculations that were previously hard to create
- leads to simpler DAX, easier maintenance, and better performance [1]
- reuse the results from its components [2]
- still in public preview
- ⇒ must be enabled manually
- are stored on the visual
- ⇒ 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]
- ⇒ they can refer to the visual structure
- ⇒ leads to more flexibility
- combine the simplicity of context from calculated columns with the on-demand calculation flexibility from measures [1]
- ⇐ the context is "visible"
- operate on aggregated data instead of the detail level [1]
- ⇒ leads to performance benefits
- 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]
- {function} COLLAPSEALL
- the calculation is evaluated at the total level of the axis [1]
- {function} EXPAND
- the calculation is evaluated at a lower level of the axis [1]
- {function}EXPANDALL
- the calculation is evaluated at the leaf level of the axis [1]
- {function} FIRST
- refers to the first row of an axis [1]
- {function} ISATLEVEL
- reports whether a specified column is present at the current level.
- {function} LAST
- refers to the last row of an axis [1]
- {function} NEXT
- refers to a next row of an axis [1]
- {function} PREVIOUS
- refers to a previous row of an axis [1]
- {function} RANGE
- refers to a slice of rows of an axis [1]
- {category} high-level functions
- {function} MOVINGAVERAGE
- adds a moving average on an axis [1]
- {function} RUNNINGSUM
- adds a running sum on an axis [1]
- {category} low-level functions
- ⇐ {exception} are available in standard DAX
- {function} INDEX
- {function} OFFSET
- {function} RANK
- {function} ROWNUMBER
- {function}WINDOW
- {function} ORDERBY
- {function} PARTITIONBY
- {function} MATCHBY
- {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
- {operation} hide calculation
- calculations that aren't needed in the visual can be hidden [2]
- {operation} copy calculation
- copies the calculation between visuals and if intermediary steps are not there, they will be copied as well [planned] [2]
- {feature} templates
- ready available calculation constructs
- {benefit} make it easier to write common calculations [1]
- {feature|planned} support for Scanner API [2]
- {parameter} axis
- influences how the visual calculation traverses the visual matrix [1]
- {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]
- {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]
- {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
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)
No comments:
Post a Comment