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 there,
I am looking to find a solution (preferably a calculated column) that will find a value of an item or an average for the closest time in Table 2 (+- 20 minutes). See example below for further clarification.
Could anyone point me to the right direction please?
Table 1
Date | Time | Item | Table 2 Match |
01/01/2022 | 01:30 | BB4 | 200 |
01/01/2022 | 01:25 | AA4 | 300 |
01/01/2022 | 02:35 | BB3 | 425 (average) |
Table 2
Date | Time | Item | Value |
01/01/2022 | 01:25 | BB4 | 200 |
01/01/2022 | 01:25 | AA4 | 300 |
01/01/2022 | 02:40 | BB3 | 450 |
01/01/2022 | 02:15 | BB3 | 400 |
Solved! Go to Solution.
Here's my take on this.
I first get the closest datetime for the same item and then average the values for the closest records with a tolerance of 20 mins.
Measure
Avg Closest =
VAR vMAXTOL = 20
VAR vDatetime = min(TABLE1[Datetime])
VAR vClosest = CALCULATE(MINX(TABLE2,abs(TABLE2[Datetime2]-vDatetime)*24*60))
VAR vAvg = AVERAGEX(filter(TABLE2,(abs(TABLE2[Datetime2]-vDatetime)*24*60)<=vClosest+0.00001 && vClosest<=vMAXTOL),TABLE2[Value])
return vAvg
Link to data and pbix file.
Here's my take on this.
I first get the closest datetime for the same item and then average the values for the closest records with a tolerance of 20 mins.
Measure
Avg Closest =
VAR vMAXTOL = 20
VAR vDatetime = min(TABLE1[Datetime])
VAR vClosest = CALCULATE(MINX(TABLE2,abs(TABLE2[Datetime2]-vDatetime)*24*60))
VAR vAvg = AVERAGEX(filter(TABLE2,(abs(TABLE2[Datetime2]-vDatetime)*24*60)<=vClosest+0.00001 && vClosest<=vMAXTOL),TABLE2[Value])
return vAvg
Link to data and pbix file.
This is brilliant Daniel - work's like magin as a measure. Do you think it would work the same as a calculated column? I would like to visualise the data and it does not seem to be working that easily. Thanks
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 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |