Forum Discussion

JSOTO_PBI's avatar
JSOTO_PBI
Frequent Visitor
9 years ago

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

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 ...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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's avatar
    JSOTO_PBI
    Frequent Visitor
    9 years ago

    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.