DistinctCount across values (ID) within same table (Master Data)

[JSOTO_PBI]

Hello,

Working with Master Data and trying to get a complete count of distinct records across groups. I'm tyring to figure out how to mimic the sql below into a power bi visualization using DAX and not wanting to duplicate/copy table. thanks in advance for your assistance.

Case: There are 8m records, 7m are unique(distinctcount), however only 250k are unique (or match) across my 3 groups. the 250k is the result i'm looking to present.

SELECT Count(distinct group1.recno) "TotalLinkedRecords"

FROM

         (select distinct recno FROM person WHERE SRCNO = 1) Group1,

         (select distinct recno FROM person WHERE SRCNO = 2) Group2,

         (select distinct recno FROM person WHERE SRCNO = 3) Group3

WHERE

            Group1.recno = Group2.recno

    AND Group2.recno = Group3.recno

    AND Group3.recno = Group1.recno

[Phil_Seamark]

Hi @JSOTO_PBI

I had some joy with the following measure, whcih can also be a table if you want to see the results.

TotalLinkedRecords = 
	COUNTROWS(
		FILTER(
			SUMMARIZE(
				'Person',
				'Person'[recno],
				"C",COUNTA('Person'[Group])
				)
				,[C]=3))

[JSOTO_PBI]

Hi @Phil_Seamark,

Thanks for the response. Much appreciate it.

This is close but not exact.  If the dataset had no dups it would result correctly. The dataset also has more than 3 groups though my use case calls to compare between 3.  

The table option helped analyze the results and showed that it's counting duplicant entries of the same group.

Is it possible to add DISTINCT of [recno] and/or a filter of [groups] (if possible an IN statement) containing only list of groups?

thanks.