Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
In a picture I will highlight the cells that should match. So correct answer for highlighted forecast cell would be 66.69.
Solved! Go to 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
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?
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
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.
@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?)
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |