Forum Discussion

louisdeg's avatar
louisdeg
New Member
4 years ago
Solved

Measure with filter and row context

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

 

  • louisdeg , try a measure like

     

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

2 Replies

  • louisdeg , try a measure like

     

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