Measure Using Foreign Table Column

[theitguy]

Hi Folks,

I am stuck with a calculation, maybe someone has an advice.

I have got the following tables:

The participant table contains all participants. Each participant is related to a group. Moreover, each participant can create n-times learning sessions. Learning sessions have a launch date and a duration in seconds (Seconds_Spend) column.

Departments

DepartmentCode	DepartmentName
12345	DemoDepartment

Participants

Participant_ID	Name	DepartmentCode
1	John Doe	12345
2	Max Mustermann	12345
3	John Smith	12345
4	Jan Janssen	12345
5	John Blow	12345
6	Juan Pérez	12345
7	Matti Meikäläinen	12345
8	Jean Dupont	12345
9	John Brown	12345

Learning_Results_Single_Sessions

Participant_ID	Launch_History_id	Launch_Date	Seconds_Spend	Status	Score
1	20984	17.11.2018	444	incomplete	0
1	23271	17.11.2018	24	incomplete	0
1	23273	19.11.2018	974	complete	36
4	20970	23.11.2018	439	incomplete	0
4	23301	23.11.2018	1	complete	66
5	20935	05.11.2018	84	incomplete	0
5	21819	05.11.2019	28	incomplete	0
5	21817	17.11.2018	72	incomplete	0
5	20983	17.11.2018	373	complete	80
5	20981	29.11.2018	10	complete	80
8	20987	03.11.2018	225	incomplete	0
8	23378	17.11.2018	148	incomplete	0
8	23386	17.11.2018	4	complete	65

Relationships:

Participants                 Departments

Departmentcode  *:1    DepartmentCode

Participants                 Learning_Results_Single_Sessions

Participant_ID       1:*     Participant_ID

I would like to calculate the average (+standard derivation) learning time per department. I want to calculate it for ALL learners in the group to make groups comparable. Unfortunately, the learners without sessions do not have any null values.

I tried a measure

AverageTime = Sum(Learning_Results_Single_Sessions[Seconds_Spend])/DISTINCTCOUNT(Participants[Participant_ID])

Moreover, I have created another table which has a relation to the departments table in which the amount of users is calcultaed for each department.

 Users_In_Groups = SUMMARIZECOLUMNS(Participants[Departmentcode];"Number_of_Users";DISTINCTCOUNT(Participants[Participant_ID]))

 

-----

If I now look at the average it is fine:

But if I apply a visual level filter via a chart, the average is no loner calculated with all nine learners but just with the four who have sessions at all:

Does anyone have some advice how to refer to all department members, even if the filter is activated?

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[theitguy]

Hi Greg,

I have tried to improve my post with sample data. I hope this helps.