Row Context - calculating looking up a value from row

[Anonymous]

Hi 

I cannot fully get row context when doing a calculation. I have created some dummy data to illustrate and hoping to get some help. This is my data, all the values in the far right column forecast use the correct formula but without row context.

Formula is =[56BM]-(([weight]-56)/0.5)

Name	Distance	BM	weight	rating	margin	RaceWeightBM	56BM	NameID	Date	Forecast
Sam	1100	75	56.5	79	2.2	72.19	72.69	1	3/05/2017 12:00:00 AM	71.69
Sam	1600	76	57	80	3.5	71.60	72.60	1	11/05/2017 12:00:00 AM	70.60
Sam	1200	83	57	78	0	83.00	84.00	1	12/06/2017 12:00:00 AM	82.00
Bill	1300	77	57	75	3.5	72.55	73.55	2	12/06/2017 12:00:00 AM	71.55
Sam	1400	78	58.5	80	0.5	77.34	79.84	1	24/06/2017 12:00:00 AM	74.84
Bill	1200	75	58	76	1.2	73.48	75.48	2	24/06/2017 12:00:00 AM	71.48
John	1100	77	56	67	3.6	72.35	72.35	3	24/06/2017 12:00:00 AM	72.35
Sam	1100	78	59			78.00	81.00	1	5/07/2017 12:00:00 AM	75.00
Bill	1100	78	58.5			78.00	80.50	2	5/07/2017 12:00:00 AM	75.50
John	1100	78	57			78.00	79.00	3	5/07/2017 12:00:00 AM	77.00

What I want to occur is.

1.  Use the NameID of the current row
2.  Use the Distance of the current row
3. Use only dates that occur before the current date
4. Locate the Max 56BM and insert that into the formula using all other values from the current row.
5. If no exact match to distance then use next closest distance
6.  No previous result or match within 400m use "n/a"

In a picture I will highlight the cells that should match. So correct answer for highlighted forecast cell would be 66.69.

[Anonymous]

The issue is that usually I would use calculate for this and filter on Distance = "1100"

However I can't know that value in advance and need the value to be obtained from the row its in. I have just read the section in Rob Collies book on Row Context but it really doesn't delve down into this level of detail.

Any ideas?

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

We can try to get the values with conditions, then we can test them with "if" statements.

Result =
VAR level1 =
    CALCULATE (
        MAX ( 'Table1'[56BM] ),
        FILTER (
            'Table1',
            'Table1'[Distance] = EARLIER ( 'Table1'[Distance] )
                && 'Table1'[NameID] = EARLIER ( Table1[NameID] )
                && 'Table1'[Date] < EARLIER ( 'Table1'[Date] )
        )
    )
VAR level2 =
    CALCULATE (
        MAX ( 'Table1'[56BM] ),
        FILTER (
            'Table1',
            'Table1'[NameID] = EARLIER ( 'Table1'[NameID] )
                && 'Table1'[Date] < EARLIER ( 'Table1'[Date] )
                && 'Table1'[Distance]
                    >= EARLIER ( Table1[Distance] ) - 200
                && 'Table1'[Distance]
                    <= EARLIER ( Table1[Distance] ) + 200
        )
    )
RETURN
    IF (
        ISBLANK ( level1 ),
        IF ( ISBLANK ( level2 ), 9999, level2 - ( [weight] - 56 ) / 0.5 ),
        level1
            - ( [weight] - 56 )
            / 0.5
    )

The columns start with "Test" are to verify  the result only.

Best Regards!

Dale

[Anonymous]

Thanks for the awesome solution. I have now learnt EARLIEST. Always amazed at DAX seems to have a really deep level of functions for almost any purpose.

[vanessafvg]

@Anonymous so just so i understand this

you want to take the max 56BM where the distance and name are equal and the date is is before the current row date,  and then run this formula Max([56BM])-(([ current row weight]-56)/0.5).  If you are unable to find the exact distance  you want the closest distance, is that higher or lower?  (and this must be within a 400m range on either side?)

[Anonymous]

Yes thats correct. The Distance should be no more than a range of 400m. So if we had 1200 anywhere from 1000 to 1400 would be acceptable.