Forum Discussion
Help with measures and AutoExists
Hi,
I have a factless fact table called Milestone and a dimension table called Project. These look as follows and are joined off the PID column.
Project
| PID | P_name |
| 1 | Proj A |
| 2 | Proj B |
Milestone
| PID | MID | Date |
| 1 | 1 | 23/03/2019 |
| 1 | 2 | 29/03/2019 |
| 1 | 3 | 16/04/2019 |
| 1 | 4 | |
| 1 | 5 | |
| 1 | 6 | |
| 1 | 7 | |
| 2 | 1 | 8/03/2019 |
| 2 | 2 | 11/03/2019 |
| 2 | 3 | |
| 2 | 4 |
I have a simple measure which counts the number of milestones for a project. It is as follows:
Measure =
CALCULATE(
COUNTROWS(Milestone),
ALL(Milestone[MID])
)
When I create a table visual consisting of Milestone[PID], Milestone[MID], [Measure]. I get the expected result. Each PID, MID combination exists and the measure correctly counts the number of milestones.
Result Table Visual
| Milestone[PID] | Milestone[MID] | [Measure] |
| 1 | 1 | 7 |
| 1 | 2 | 7 |
| 1 | 3 | 7 |
| 1 | 4 | 7 |
| 1 | 5 | 7 |
| 1 | 6 | 7 |
| 1 | 7 | 7 |
| 2 | 1 | 4 |
| 2 | 2 | 4 |
| 2 | 3 | 4 |
| 2 | 4 | 4 |
When I remove Milestone[PID] and replace it with the Project[P_name] I get the correct result but with combinations which should not exists (PID2 does not have milestones 5,6,7 ). From my understanding this is caused by the AutoExist feature not operating on columns in different tables and effectively creating a cross join.
Result Table Visual
| Project[P_name] | Milestone[MID] | [Measure] |
| Proj A | 1 | 7 |
| Proj A | 2 | 7 |
| Proj A | 3 | 7 |
| Proj A | 4 | 7 |
| Proj A | 5 | 7 |
| Proj A | 6 | 7 |
| Proj A | 7 | 7 |
| Proj B | 1 | 4 |
| Proj B | 2 | 4 |
| Proj B | 3 | 4 |
| Proj B | 4 | 4 |
| Proj B | 5 | 4 |
| Proj B | 6 | 4 |
| Proj B | 7 | 4 |
How can I write the measure to get a result where only the correct combinations exist? I have found many articles explaining this "feature" but nothing explaining how to get around it. Creating a similar measure in my production model creates cross joins so big that Power Bi can't handle it.
Note that adding the P_name column to Milestone table is not possible and nor is including Milestone[PID] in the result visual.
Desired Result Visual
| Project[P_name] | Milestone[MID] | [Measure] |
| Proj A | 1 | 7 |
| Proj A | 2 | 7 |
| Proj A | 3 | 7 |
| Proj A | 4 | 7 |
| Proj A | 5 | 7 |
| Proj A | 6 | 7 |
| Proj A | 7 | 7 |
| Proj B | 1 | 4 |
| Proj B | 2 | 4 |
| Proj B | 3 | 4 |
| Proj B | 4 | 4 |
Many thanks!