cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
say_1839
Regular Visitor

Measure For Summing Values from Related Table

Hi all,

 

I've been struggling to write a measure for the situation below. I have a Projects table, in which various projects and the project length in days is listed. I then have a second table, Status, which has some of the projects listed with their status. I want to write a measure that sums the length of the projects which are listed as Active in the Status table. Could anyone point me in the right direction?

Note there is a Many to one relationship between Projects and Status, cross filter direction is single, where Projects filters Status. Also, the Status table has the same project listed multiple times due to this table being used for other purposes, but the Status will always be the same for the same project.

 

Projects

Project NameProject Length
Project1100
Project2500
Project3400
Project4250

 

Status

Project NameStatus
Project2Active
Project2Active
Project2Active
Project3Delayed
Project3Delayed
Project3Delayed
Project4Active
Project4Active
Project4Active

 

Thank you!

1 ACCEPTED SOLUTION

HI @say_1839 ,

 

Make your relationship bi-directional as in the DAX we are trying to filter Project table based ona check from Status table.

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




View solution in original post

4 REPLIES 4
Pragati11
Super User
Super User

Hi @say_1839 ,

 

You can use a DAX as follows:

 

ActiveSum = CALCULATE(SUM('Projects'[Project Length]), 'Status'[Status] = "Active")

 

Check if the above dax works.

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Hi @Pragati11 ,

 

Thank you for taking a look at this and your response! However, I am still getting a number that is too high using the DAX code you provided. I am expecting 750 days based on the data, but the measure is calculating 1250 days. Any thoughts where I might be going wrong here? Could it be that in the Status table, each project is listed multiple times?

say_1839_0-1593514130341.pngsay_1839_1-1593514166599.png

 

Your advice is very much appreciated!

HI @say_1839 ,

 

Make your relationship bi-directional as in the DAX we are trying to filter Project table based ona check from Status table.

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




View solution in original post

Worked perfectly! Thank you @Pragati11 !

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!