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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
theitguy
Helper I
Helper I

Measure Using Foreign Table Column

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

DepartmentCodeDepartmentName
12345DemoDepartment

 

 

Participants

Participant_IDNameDepartmentCode
1John Doe12345
2Max Mustermann12345
3John Smith12345
4Jan Janssen12345
5John Blow12345
6Juan Pérez12345
7Matti Meikäläinen12345
8Jean Dupont12345
9John Brown12345

 

Learning_Results_Single_Sessions

Participant_IDLaunch_History_idLaunch_DateSeconds_SpendStatusScore
12098417.11.2018444incomplete0
12327117.11.201824incomplete0
12327319.11.2018974complete36
42097023.11.2018439incomplete0
42330123.11.20181complete66
52093505.11.201884incomplete0
52181905.11.201928incomplete0
52181717.11.201872incomplete0
52098317.11.2018373complete80
52098129.11.201810complete80
82098703.11.2018225incomplete0
82337817.11.2018148incomplete0
82338617.11.20184complete65

 

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

 screen0.PNG

 

 

-----

If I now look at the average it is fine:

screen1.PNG

 

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:

screen2.PNG

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

2 REPLIES 2
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors