- Purpose: Modifies the context in which data is evaluated.
- Usage: Often used to apply filters to calculations or change the context of an aggregation.
- Example: CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West")
- Why it's important: This function is key for creating dynamic measures and performing complex calculations that require altering the default context.
- Purpose: Returns a table that has been filtered based on a condition.
- Usage: Used to filter data in complex calculations, often combined with other functions like CALCULATE.
- Example: FILTER(Sales, Sales[Amount] > 500)
- Why it's important: Provides control over rows to include in calculations, making it powerful for row-level filtering.
- Purpose: Performs row-by-row calculations and then sums the results.
- Usage: Often used for calculations that need to be done at the row level, such as calculating the product of two columns.
- Example: SUMX(Sales, Sales[Quantity] * Sales[Price])
- Why it's important: Crucial for custom aggregations and calculations involving multiple columns or complex operations.
- Purpose: Fetches a value from a related table in a one-to-many relationship.
- Usage: Used to bring values from related tables for calculation purposes.
- Example: RELATED(Product[ProductName])
- Why it's important: This function allows you to access data across related tables, making it vital for working with complex data models.
- Purpose: Removes filters from a table or column.
- Usage: Often used to clear filters in calculations, allowing calculations to ignore the current filter context.
- Example: CALCULATE(SUM(Sales[Amount]), ALL(Sales[Region]))
- Why it's important: Essential for performing calculations across the entire dataset, bypassing filters that would otherwise limit the scope.