Forum Discussion
Row Context - calculating looking up a value from row
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.
- Use the NameID of the current row
- Use the Distance of the current row
- Use only dates that occur before the current date
- Locate the Max 56BM and insert that into the formula using all other values from the current row.
- If no exact match to distance then use next closest distance
- 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.
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
5 Replies
- vanessafvg
Community Champion
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?)
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-msftMicrosoft Employee
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
