Average duration on join in DAX

Question

I try to calculate in DAX the average duration of a join table on lead (t1= inboundcdr , t2= orders. I want to calculate the avg duration of calls on the inboundcdr table who make orders in table orders.

This is Dax I try to run. The var join is right on the number of rows but now I want to extract the avg in this table in the duration column

AverageDuration = 
VAR Join = 
    FILTER(
        CROSSJOIN(inboundCdr, orders), 
        inboundCdr[lead] = orders[leadId]
    )
RETURN
    AVERAGEX(Join, [duration])

The join is right because I verify with db on countrows dax, but I don't know how to calculate de average.

I'm trying to replicate this SQL query:

    SELECT avg(i.duration)
FROM orders o
inner join inboundCdr i on i.lead = o.leadId 

Answer 1

join is a reserved word in DAX, so rename your VAR and it should be good.

AverageDuration = 
  VAR j = 
    FILTER(
        CROSSJOIN(inboundCdr, orders), 
        inboundCdr[lead] = orders[leadId]
    )
  RETURN
    AVERAGEX(j, [duration])