Average with condition from another table

[Jrose]

Hello,

I have spent all day trying various solutions to this and still can't get anything to work.  If there is another post with a solution already, please point me to it.

I've got two tables (examples below), one by person and year code with some other columns.  The other is a look-up table, one record per year code with additional codes and descriptors.  These are in the model, linked many-to-one by the year code.

What I need is the average count of person_id per year_part.  The person count is NOT distinct, it really is how many total.  The year_part seems to group ok by dropping it in the Group part of the visualization.

This almost works

mAvgYrPart = divide(count('table1'[person_id]),count('table2'[year_part]))

What I want to get, using the example, is average = 2

so that the count of person_id where table1.year_part=10  is 4  (year_part is actually coming through the relationship to table2 via year_code)

count of year_part where table2.year_part = 10 is 2
so average is 4/2 = 2

The problem is that I would get an average of 1, because the look-up table contains future years so there are actually four records where year_part = 10 and the calculation becomes 4/4 = 1.

How can it count table2.year_part where table2.year_code is less than or equal to max(table1.year_code)?  Oh, year_code is a number so MAX should work.  Year_part is text, just a label.

Seems it should be easy, but I've tried lookupvalue to pull the year_part to table1 first which kept giving errors, Related gave an error about ambiguity,  various combinations using Calculate, Summarize, average, quick measures keep giving errors or simply don't work correctly.  Started using DAX last week so not sure what to try next - any suggestions are appreciated.

table 1

person_id	year_code	fact1	fact2
1	201810	A	A
1	201810	A	B
1	201820	A	B
2	201810	A	C
2	201820	A	A
2	201820	A	B
2	201910	B	A

table 2

year_code	year_part
201810	10
201820	20
201830	30
201910	10
201920	20
202010	10
202020	20
202110	10
202120	20

[wdx223_Daniel · Accepted solution]

[Jrose]

Awesome! Thank you so much.  

I used the exact formula you showed replaced with actual table and field names and it works perfectly, even with additional groups and filters on the visual.

FYI - the output you show isn't what I needed so I'm not sure how that happened.  But as said, it is doing what was needed with the real data, so thanks.