A DAX function for safe division: when the denominator is zero or BLANK it gives BLANK, or a fixed alternative value if you supply one. It is preferred over the / operator whenever the denominator isn't a constant.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains DIVIDE in context, with comparison tables and the common traps.
Terms in this definition
- DAX
Short for Data Analysis Expressions, the formula language you write measures, calculated columns and row-level security rules in, within tabular semantic models in Power BI, Excel Power Pivot or Analysis Services.
- BLANK
DAX uses BLANK to mean no value at all. It resembles NULL in SQL but behaves more like an empty cell in Excel, so adding 20 to it returns 20; the BLANK function creates one and ISBLANK detects it.
- OVER
Gives a T-SQL window function its window: PARTITION BY, ORDER BY and, if wanted, a ROWS or RANGE frame. Rankings and running totals can then be worked out while every row is kept.
Related terms
- ANSI mode
A stricter Spark SQL behaviour, enabled by default since Apache Spark 4.0 (so also in Fabric Runtime 2.0), where a bad cast or an integer overflow fails the query rather than quietly producing null. Functions such as
try_cast,try_addandtry_dividekeep the old null result, while switching ANSI mode off should be treated as a temporary workaround. - IF
Tests a condition in DAX and gives back one result when it is TRUE, a different result when it is FALSE. To avoid errors from zero denominators, DIVIDE is the better-optimised option rather than wrapping the division in IF.
- IFERROR
Catches an error in a DAX expression and returns a fallback value. Like ISERROR it triggers additional storage engine scans, so cleaning data upstream in Power Query, testing with IF, or calling DIVIDE are better options.
- Source group
Composite models divide items by origin: everything from one DirectQuery source forms one group, while import plus Direct Lake items together form another. Relationships spanning two groups are always limited.