Forum Discussion
Calculated Column IF condition to get Average Data from One Table on to Another Table
Hi All,
I have two tables, joined at on Date with Many to Many cardinalities. 'Table A' has the data in 5 minutes buckets and 'Table B' has the data at a minute level. I want to compare the 'Time_IN' from 'Table B' with StartTime and EndTime from Table A' to get the average of 'Info_Value' for every 5 minutes from 'Table B' on to 'Table A'
TABLE A
| Date | StartTime | EndTime |
| 01/19/20 || | 01/19/20 10:00:00 || | 01/19/20 10:05:00 |
| 01/19/20 || | 01/19/20 10:05:00 || | 01/19/20 10:10:00 |
| 01/19/20 || | 01/19/20 10:10:00 || | 01/19/20 10:15:00 |
| 01/20/20 || | 01/19/20 10:15:00 || | 01/19/20 10:20:00 |
TABLE B
| Date | Time_IN | Info_Value |
| 1/19/20 || | 1/19/20 10:00:00 || | 3 |
| 1/19/20 || | 1/19/20 10:01:00 || | 4 |
| 1/19/20 || | 1/19/20 10:02:00 || | 5 |
| 1/19/20 || | 1/19/20 10:03:00 || | 6 |
| 1/19/20 || | 1/19/20 10:04:00 || | 11 |
| 1/19/20 || | 1/19/20 10:05:00 || | 12 |
| 1/19/20 || | 1/19/20 10:06:00 || | 10 |
The Dax query I wrote is giving the overall average of Table B
Expected Output
| Date | StartTime | EndTime | New_Column |
| 01/19/20 || | 01/19/20 10:00:00 || | 01/19/20 10:05:00 || | 8.2 |
| 01/19/20 || | 01/19/20 10:05:00 || | 01/19/20 10:10:00 || | ... |
| 01/19/20 || | 01/19/20 10:10:00 || | 01/19/20 10:15:00 || | ... |
| 01/20/20 || | 01/19/20 10:15:00 || | 01/19/20 10:20:00 || | ... |
Anonymous , Try a new column in table A Like
AVERAGEX(FILTER(Table B, 'Table B'[timeIN] >= 'Table A'[start_date_time] && 'Table B'[timeIN] <= 'Table A'[End_date_time]),'Table B'[Info_Value])
2 Replies
Anonymous , Try a new column in table A Like
AVERAGEX(FILTER(Table B, 'Table B'[timeIN] >= 'Table A'[start_date_time] && 'Table B'[timeIN] <= 'Table A'[End_date_time]),'Table B'[Info_Value])
amitchandak Thanks that worked, and resolved my issue.