Distinct Count of Value Where Distinct Count of Another Value >1 And Value = Value

[StephenK]

Hey all,

I need to write a DAX formula to get a slightly complicated distinct count.

Here is a sample table:

PersonId	VisitId	ReportId
1	95	999
1	95	1000
1	95	1001
2	100	545
3	123	697
3	23	1201

I need a distinct count of PersonId where distinct count of ReportId>1 on the same VisitId

So for example PersonId 1 has 3 distinct ReportId's on VisitId 95, so only that id should be counted in the formula. PersonId 2 and PersonId 3 do not have multiple ReportId's on the same VisitId so they should not be counted. Hope that makes sense.

Any help would be appreciated!

[Anonymous · Accepted solution]

[Measure] =
CALCULATE(
	DISTINCTCOUNT( T[PersonId] ),
	FILTER(
		// This table grabs all the existing
		// combinations of person-visit visible
		// in the current context.
		SUMMARIZE(
			T,
			T[PersonId],
			T[VisitId]
		),
		// This condition just filters the pairs
		// where the person has at least 2 reports
		// for the currently interated visit.
		CALCULATE(
			DISTINCTCOUNT( T[ReportId] ) > 1
		)
	)
)

Best

D