Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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))
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |