CALCULATE FUNCTION CONFUSION ON ROW CONTEXT VS FILTER CONTEXT

[Bambroo69]

I have a confusion on the backend calculation for the CALCUATE MEASURE.   I tried to create a CALCULATE measure in one of my report, see below.

 Here is the DAX Measure: DSA After LT Change = CALCULATE(DIVIDE(SUM(Merged[SHIPRESULT]),SUM(Merged[PO_LINES])), Merged[PO CUT DATE]> Merged[Change Effective Date])

The Change effective date column has two dates 2/6/2024 and 5/3/2024. How does Measure know which Date to pick for comparison with PO Cut date unless it iterates row by row. But DAX Measures, do not iterate row by row unless we use the iterator function. 

Can anyone please clarify?                                                       

[hackcrr · Accepted solution]

Hi, @Bambroo69 

In Power BI's DAX, the CALCULATE function is used to modify the computational context, and it accepts one or more parameters, which can be filters, row context modifiers, etc.Merged[PO CUT DATE] > Merged[Change effective Date] This part is a Boolean expression that defines a filter condition, but this condition is not directly applicable to row-by-row comparisons. In fact, when you use this metric in a report, the DAX engine evaluates all rows in the Merged table based on the current computational context (including any slicers, filters, or other visual objects on the report page) and this filter condition that you provide.
This filter condition is applied to the Merged table as an additional filter, excluding rows whose PO CUT DATE is less than or equal to the Change effective Date. SUM(Merged[SHIPRESULT]) and SUM(Merged[PO_LINES]) are then calculated based on this new filtered data set.
It's important to understand that DAX doesn't really iterate row-by-row (although sometimes it behaves as if it does). Instead, it performs computations on the entire dataset based on the computation context and the defined set of filters. In the metric you provided, Merged[CHANGE EFFECTIVE DATE] is not compared row-by-row to Merged[PO CUT DATE], but rather the entire table (or a portion of it based on the current context) is filtered to exclude rows that don't qualify.
In other words, the DAX engine will first apply any existing filters (e.g., slicers, filter panels, etc.), then apply the additional filters that you specify in the CALCULATE function (i.e., Merged[PO CUT DATE] > Merged[Change effective Date]), and finally compute the metric based on those filters.
If you want to have multiple metrics based on different Change effective Date values, you may need to create a table to store these dates and use the RELATED or RELATEDTABLE functions to get the corresponding dates based on some relationship, or use a combination of SUMX and FILTER for more complex row-level calculations. But in this simple example, the metrics you gave are sufficient to implement calculations based on the filtering criteria you defined.

[v-huijiey-msft · Accepted solution]

Hi @Bambroo69 ,

Thanks for the reply from @hackcrr .

Apply filter conditions in the CALCULATE function: Merged[PO CUT DATE]> Merged[Change Effective Date]

DAX applies this filter to the entire table rather than row by row, effectively considering all rows with a "PO CUT DATE" greater than any "Change Effective Date".

This is a filter context operation, not a row context operation.

You can try modifying the syntax as follows:

DSA After LT Change = CALCULATE(
    DIVIDE(
        SUM(Merged[SHIPRESULT]),
        SUM(Merged[PO_LINES])
    ), 
    FILTER('Merged',
    MAX(Merged[PO CUT DATE]) 
    > 
    MAX('Merged'[Change Effective Date]))
)

The page effect after similar modifications is as follows:

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[hackcrr · Accepted solution]

Hi, @Bambroo69 

In Power BI's DAX, the CALCULATE function is used to modify the computational context, and it accepts one or more parameters, which can be filters, row context modifiers, etc.Merged[PO CUT DATE] > Merged[Change effective Date] This part is a Boolean expression that defines a filter condition, but this condition is not directly applicable to row-by-row comparisons. In fact, when you use this metric in a report, the DAX engine evaluates all rows in the Merged table based on the current computational context (including any slicers, filters, or other visual objects on the report page) and this filter condition that you provide.
This filter condition is applied to the Merged table as an additional filter, excluding rows whose PO CUT DATE is less than or equal to the Change effective Date. SUM(Merged[SHIPRESULT]) and SUM(Merged[PO_LINES]) are then calculated based on this new filtered data set.
It's important to understand that DAX doesn't really iterate row-by-row (although sometimes it behaves as if it does). Instead, it performs computations on the entire dataset based on the computation context and the defined set of filters. In the metric you provided, Merged[CHANGE EFFECTIVE DATE] is not compared row-by-row to Merged[PO CUT DATE], but rather the entire table (or a portion of it based on the current context) is filtered to exclude rows that don't qualify.
In other words, the DAX engine will first apply any existing filters (e.g., slicers, filter panels, etc.), then apply the additional filters that you specify in the CALCULATE function (i.e., Merged[PO CUT DATE] > Merged[Change effective Date]), and finally compute the metric based on those filters.
If you want to have multiple metrics based on different Change effective Date values, you may need to create a table to store these dates and use the RELATED or RELATEDTABLE functions to get the corresponding dates based on some relationship, or use a combination of SUMX and FILTER for more complex row-level calculations. But in this simple example, the metrics you gave are sufficient to implement calculations based on the filtering criteria you defined.