Calculated column DAX for average of filtered related table based on current row value

[vissvess]

Hi,

I need a dax expression for following situation.

Duration Table:

Process Ref    Duration

545454             25

545454             54

217544             25

545454             24

and so on...

Another table with unique process ref number

Process ref table

Process Ref    Col1  Average

545454             XX      (Averae of particular process ref durations from duration table)

542187             XX      (Averae of particular process ref durations from duration table) 

217544             XX      (Averae of particular process ref durations from duration table) 

844541             XX      (Averae of particular process ref durations from duration table) 

and so on...

I tried the following expression which doesnot turns out.

Average of duartion column =
CALCULATE (
    AVERAGE ( Duration table[Duration] ),
    Duration table[Process ref] = Process ref table[Process ref]
)

My code returns only error. Any help would be appreciated.

Thanks in advance.

[Anonymous · Accepted solution]

-- If there is a many-to-one relationship
-- between 'Duration Table' and 'Process ref
-- table', then:

[Average of Duration] =
CALCULATE (
	AVERAGE ( 'Duration Table'[Duration] ) 
)

-- If the cardinality of the Process ref
-- table is huge, in the range of tens
-- of thousands and up, then the above
-- will be slow because it has to execute
-- a context transition. You can also
-- use the following in this case:

[Average of Duration] =
var __currentProcess = 'Process ref table'[Process ref]
var __average =
	AVERAGEX(
		FILTER(
			'Duration Table'[Duration],
			'Duration Table'[Process ref] = __currentProcess
		),
		'Duration Table'[Duration] 
	)

Best

Darek

[vissvess]

@Anonymous ,

Your earlier support for tremendous. Kindly support in this case.