Forum Discussion
And and Not Between Two Tables
I having a very hard time figuring out how to solve this:
The goal is to figure out how many projects are waiting on specific parts. This is to be done by looking at what projects have completed the preceeding task but have not completed the task where the part is required.
I have two tables
Table 1 is a list of project number, tasks, and completion dates.
Table 2 is a list of Part Name, Required Preceeding Task, Task Requiring Part.
Table 2 is only about 10 lines of parts im trying to track but table 1 is about 15 million lines.
I would love to solve this through relating tables, but drawing a blank that route, Ive regressed to just forgetting the second table and writing a measure for each part listed.
For the DAX I've tried the below with no luck.
Part 1 QTY =
CALCULATE(DISTINCTCOUNT('Project Number'),
AND ('Task' = "Phase 1 Complete", NOT(ISBLANK('Task Complete Date'))))
-
CALCULATE(DISTINCTCOUNT('Project Number'),
AND ('Task' = "Phase 2 Start", ISBLANK('Task Complete Date'))))
Falling back on measures to solve this is not preffered but it is managable. But I cant even get the DAX to work.
Any help would be greatly appreciated. Thanks,
2 Replies
- AnonymousNot applicable
I got some DAX to solve my issue.
Delta Count = CALCULATE(DISTINCTCOUNT('Project Number', AND ('Task Name' = "Phase 1 Complete", 'Task Complete Date' <> BLANK())) - CALCULATE(DISTINCTCOUNT('Project Number', AND ('Task Name' = "Phase 2 Start", 'Task Complete Date' <> BLANK()))- V-lianl-msftCommunity Support
Hi Anonymous ,
Glad to you have solved the problem. Thanks for sharing your solution.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.