DAX to average values from another table using associations

Question

Suppose I have these three tables in Power BI (a simplification from my enterprise app):

Table_A

Coordinate	Value
A1	12
A1	15
A1	17
A2	13
A2	19

Table_B

Coordinate	Value
B1	90
B1	91
B1	88
B1	87
B2	80
B2	88
B3	91
B4	99
B4	99
B4	98
B4	92

Associations

Coordinate_A	Coordinate_B
A1	B1
A1	B2
A1	B3
A2	B3
A2	B4

I'd like to use DAX to get an average of all B values associated with each A coordinate. So A1 has B1, B2 and B3 assocated with it (from the Associations table). So for A1, I'd like an average of all values with coordinates B1, B2, B3 from Table_B. For A2, I'd like an average of all B3 and B4 values from Table_B.

So the desired result in Power BI would look like this:

Coordinate A	Average of A	Average of B
A1	14.667	87.857
A2	16	95.8

Can someone help with a DAX formula to give [Average of B] -- obviously grouped by [Coordinate A]? NOTE: I'm using Direct Query against an SSAS tabular model in Power BI, and TREATAS (and some other functions) are not recognized.

snk550 snk550 · Accepted Answer · 2021-02-03T07:07:31

Assuming the model looks like this with 2 inactive many-to-many relationships:

You could use this measure to get Average of B:

Average of B = 
CALCULATE (
    AVERAGE ( tbl2[Value] ),
    USERELATIONSHIP ( Assc[Coordinate_A], tbl1[Coordinate] ),
    USERELATIONSHIP ( Assc[Coordinate_B], tbl2[Coordinate] )
)

Result:

DAX to average values from another table using associations

1 Answers