Calculated Column IF condition to get Average Data from One Table on to Another Table

[Anonymous]

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

NEW_Column = IF(

COUNTROWS(

FILTER(Table B, 'Table B'[timeIN] >= 'Table A'[start_date_time] && 'Table B'[timeIN] <= 'Table A'[End_date_time])) > 0, AVERAGE('Table B'[Info_Value]))

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 ||	...

[amitchandak · Accepted solution]

@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])

[Anonymous]

@amitchandak Thanks that worked, and resolved my issue.