Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 Name | Project Length |
Project1 | 100 |
Project2 | 500 |
Project3 | 400 |
Project4 | 250 |
Status
Project Name | Status |
Project2 | Active |
Project2 | Active |
Project2 | Active |
Project3 | Delayed |
Project3 | Delayed |
Project3 | Delayed |
Project4 | Active |
Project4 | Active |
Project4 | Active |
Thank you!
Solved! Go to 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
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
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?
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
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |