Max Sales Calculation with DAX

Complex DAX Formula Using CROSSFILTER, MAXX, and CALCULATE

Formula Description

The provided formula aims to filter and calculate the maximum sales amount from a fact table (such as Sales) based on the cross-filtered context from a related dimension table (such as Products). The formula uses the CROSSFILTER, MAXX, and CALCULATE functions to achieve this.

DAX Formula

Max_Sales_By_Product = 
CALCULATE(
    MAXX(
        Sales,
        Sales[Amount]
    ),
    CROSSFILTER(
        Sales[ProductID],
        Products[ProductID],
        BOTH
    )
)

Explanation

1. MAXX Function:

   • MAXX(<Table>, <Expression>): This calculates the maximum value of an expression evaluated over a table. Here, it is used to determine the maximum value in the Sales[Amount] column.

2. CALCULATE Function:

   • CALCULATE(<Expression>, <Filter1>, <Filter2>, ...): This modifies the context in which the data is evaluated. Here, it is used to calculate the maximum sales amount (MAXX(Sales, Sales[Amount])) under the new context created by the CROSSFILTER.

3. CROSSFILTER Function:

   • CROSSFILTER(<Column1>, <Column2>, <Direction>): This function controls the cross-filter direction for a relationship between two columns. It is set to BOTH, allowing bi-directional filtering between the Sales[ProductID] and Products[ProductID] columns.

Practical Example

Consider two tables: Sales and Products.

Sales Table:

Products Table:

Implementation Context

1. Data Model:

   • Ensure there is a relationship defined between the Sales[ProductID] and Products[ProductID] columns.
   • The relationship should initially be set to single-direction (SINGLE).

2. Usage:

   • Place this measure in a Pivot Table or any visualization to see the maximum sales amount for each product from the Products table.

Final Notes

• Ensure that the tables and relationships are properly established in the data model to allow the formula to function correctly.
• This formula is especially useful when the report requires insights from cross-filtered relationships between dimension and fact tables.

For further learning and in-depth DAX training, consider exploring courses on the Enterprise DNA platform.