Measure with filter and row context

louisdeg — Mon, 04 Apr 2022 11:41:25 GMT

Hi all,

I have a Powerbi issue where I need some help. Consider the following data tables:

Data Table 1: Employee list

Name	ID	Manager ID	Job title
John	1		CEO
Jane	2	1	CTO
Elliot	3	1	CFO
Elaine	4	1	COO
Vicky	5	2	SW Engineer

Data Table 2: Question List

Question

Question 1

Question 2

Data Table 3: Survey Responses

Name	ID	Score	Question
John	1	1	Question 1
Jane	2	-1	Question 1
Elliot	3	0	Question 1
Elaine	4	-2	Question 1
Vicky	5	2	Question 1
John	1	0	Question 2
Jane	2	2	Question 2
Elliot	3	2	Question 2
Elaine	4	2	Question 2
Vicky	5	-2	Question 2

I already have a measure SURVEY SCORE that calculates the overall respondent survey score across questions, depending on the filter context:

SURVEY SCORE = AVERAGEX('Question List', average('Survey Responses'[Score]))

I now want to build a measure MANAGER SCORE that calculates for managers the average score of all their direct reports within a particular filter context. See below examples with a powerBI visual table

Filter context 1: Questions = all

Name	ID	MANAGER SCORE
John	1	=[(-1+0-2)/3+(2+2+2)/3]/2 = 1/2
Jane	2	[2 + (-2)] / 2 = 0
Elliot	3	N/A
Elaine	4	N/A
Vicky	5	N/A

Filter context 2: Questions = Question 1

Name	ID	MANAGER SCORE
John	1	=(-1+0-2)/3 = -1
Jane	2	2
Elliot	3	N/A
Elaine	4	N/A
Vicky	5	N/A

My original thought was to write something like this:

MANAGER SCORE = calculate([SURVEY SCORE],FILTER('Employee List', 'Employee List'[Manager ID]= *employee ID fed within visual*))

However I have not found a way to write this formula. Would someone be kindly be able to help me?

PS:

I know I can write in a calculated column

MANAGER SCORE = calculate([SURVEY SCORE],FILTER('Employee List', 'Employee List'[Manager ID]= EARLIER('Employee List'[ID]))) but this does not adapt to filter context

Re: Measure with filter and row context

amitchandak — Mon, 04 Apr 2022 12:22:31 GMT

louisdeg , try a measure like

MANAGER SCORE = calculate([SURVEY SCORE],FILTER(allselected('Employee List'), 'Employee List'[Manager ID]= max('Employee List'[ID])))

Re: Measure with filter and row context

louisdeg — Mon, 04 Apr 2022 12:31:08 GMT

Wow, I think it worked! Thanks a lot amitchandak !!

topic Re: Measure with filter and row context in DAX Commands and Tips

Measure with filter and row context

Re: Measure with filter and row context

Re: Measure with filter and row context