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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Worked perfectly! Thank you @Pragati11 !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors