Day 7: DAX — Essential Functions

SUM(table[column]) simply adds all values in a single column — it is an aggregation function. SUMX(table, expression) iterates row by row through a table, evaluates an expression for each row, and then sums the results. Use SUMX when the value to sum does not exist as a column and must be calculated per row. Example: if your table has Quantity and UnitPrice but no Revenue column, use SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) to calculate total revenue without adding a calculated column.

ALL(table or column) removes all filters from a table or specific column, ignoring whatever slicers or visual filters are active. Combined with CALCULATE: Total Revenue All Regions = CALCULATE(SUM(Sales[Revenue]), ALL(Sales[Region])). This gives total revenue regardless of the Region filter — even if a slicer filters to "North", this measure still shows the grand total. Used in % of total calculations: Revenue % = DIVIDE(SUM(Sales[Revenue]), CALCULATE(SUM(Sales[Revenue]), ALL(Sales[Region]))).

Revenue % of Total = DIVIDE( SUM(Sales[Revenue]), CALCULATE(SUM(Sales[Revenue]), ALL(Sales[Product])) ) DIVIDE is preferred over / because it handles divide-by-zero gracefully (returns 0 or blank instead of an error). Format the measure as Percentage. The ALL(Sales[Product]) removes the product filter so the denominator always shows the grand total, regardless of which product row or bar is being evaluated.

RELATED retrieves a value from a related table in a many-to-one relationship. Used in calculated columns: if your Sales table has a CustomerID and your Customer table has CustomerName, you can create a calculated column: Customer Name = RELATED(Customer[CustomerName]). This works like a VLOOKUP — for each row in Sales, it looks up the matching CustomerName from the Customer table following the defined relationship. RELATED only works in the direction from many to one (from fact to dimension).

COUNTROWS with FILTER: Count North Orders = COUNTROWS(FILTER(Sales, Sales[Region] = "North")). CALCULATE with COUNTROWS: Count North Orders = CALCULATE(COUNTROWS(Sales), Sales[Region] = "North"). Both return the same result — the second is generally faster. COUNTIF equivalent for specific value: CALCULATE(COUNTROWS(Sales), Sales[Status] = "Returned"). DISTINCTCOUNT counts unique values: Unique Customers = DISTINCTCOUNT(Sales[CustomerID]).