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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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!:
Mastering Power BI 2nd Edition

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.