Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
vissvess
Helper V
Helper V

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

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

-- 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
Helper V
Helper V

@Anonymous ,

 

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

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.