cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION
Super User

@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 2
Super User

@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
Not applicable

@amitchandak Thanks that worked, and resolved my issue.