Line and column contexts in PowerBI measures?

Question

I am trying to replicate a calculation originally made in Excel, using PowerBI and although I have read some documentation about context calculation in measures, I am struggling to get the results.

My table would like as the one below

Date	Weight	Calculated_measure
01/01/2020	1	0
01/02/2020	3	31
01/03/2020	3	147

Dates are in dd/mm/yyyy.

The idea is the following for the calculated measure:

For each line I compare the current “Date” value with all other “Date” values and make a subtraction.
If the result of the subtraction is greater than zero, I keep the result. Otherwise, I set the result to 0.
Then I multiply the results by the weight

To illustrate the calculation of the third line: (01/03/2020-01/01/2020)*1 + (01/03/2020-01/02/2020) *3 + (01/03/2020-01/03/2020) *2 =60 * 1 + 29 * 3 + 0 = 147

The formula in Excel for the first line, for example: = { SUMPRODUCT(
IF ( (A2-$A$2:$A$4)>0; (A2-$A$2:$A$4); 0 ) ; $B$2:$B$4 ) }

Thank you. Understanding this code will help me a lot to learn context filters in PowerBI

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-08T15:33:37

The confusing bit in DAX is how to know which row context to use.

You can use the EARLIER function to move back from the sumproduct row context to the earlier original row context and define your measure like this:

CALCULATED_COLUMN = 
SUMX (
    Table1,
    IF (
        EARLIER ( Table1[DATE] ) > Table1[DATE],
        EARLIER ( Table1[DATE] ) - Table1[DATE]
    ) * Table1[WEIGHT]
)

If you don't want to worry about remembering how EARLIER works, you can use a variable to define the original row context as a constant and use that in the iterator:

CALCULATED_COLUMN = 
VAR RowDate = Table1[DATE]
RETURN
    SUMX (
        FILTER ( Table1, Table1[Date] < RowDate ),
        ( RowDate - Table1[DATE] ) * Table1[WEIGHT]
    )

In the above, I applied a filter instead of using an IF. This is typically more efficient when applicable.

Edit: In the above, I was thinking in terms of a calculated column. For a measure, the situation is slightly different and OP's comment below is the appropriate adjustment:

MEASURE =
VAR RowDate = SELECTEDVALUE ( Table1[DATE] )
RETURN
    SUMX (
        FILTER ( ALL ( Table1 ), Table1[Date] < RowDate ),
         ( RowDate - Table1[Date] ) * Table1[WEIGHT]
    )

The SELECTEDVALUE is needed since row context doesn't exist for a measure and ALL is needed to apply the sum-product to the whole table instead of just the part in the filter context.

Line and column contexts in PowerBI measures?

1 Answers