Help with measures and AutoExists

[Anonymous]

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!

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

You can try to use following measure to achieve your requirement:

Measure = 
CALCULATE(
    COUNTROWS(Milestone),
    ALLSELECTED(Milestone[MID]),
    VALUES(Milestone[PID])
)

Regards,

Xiaoxin Sheng