Forum Discussion
DistinctCount for matching values on two columns
Guys I tried all proposed solution but none worked for me.
I tried to literally copy yours approach but none worked. I think the issue is that I have a many to many relationship (with a table in between). I perhaps didn't give a detail that makes the formula more difficult.
Table 1: actual hours worked on a given taskID in a given day
Table 2: planned hours on a given taskID in a given day
Table 3: list of all TaskID with TaskName
Table 1: RegistrationDate, ID, Quantity
Table 2: PlanningDate, ID, Quantity
Table 3: ID, IDName
In fact, I need to:
- count how many IDs we actually worked that were planned
- sum the hours actually worked in the IDs that were planned.
Hi, allejot
We don't recommend you relate many-to-many dimension-type tables directly. Please refer to the following guidance:
- Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column
- Add a bridging table to store associated entities
- Create one-to-many relationships between the three tables
- Configure one bi-directional relationship to allow filter propagation to continue to the fact-type tables
- When it isn't appropriate to have missing ID values, set the Is Nullable property of ID columns to FALSE—data refresh will then fail if missing values are sourced
- Hide the bridging table (unless it contains additional columns or measures required for reporting)
For further information, you may refer to the document .
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.