Forum Discussion
DAX code: Find duplicates based on another column and count duplicates.
This is a follow up to a previous post:
Using DAX
I need to see which accounts have both Y and N in the 'New' column.
These accounts will be shown in a new table along with the NewID column.
As well as that I need a count of the number of times NewID appears.
So account '123' both has Y and N and the newID is 5
Also account '789' also has Y and N and and the newID is also 5. So the NewID has appeared twice.
So the output will look like this:
Many thanks for your time.
Anonymous
Create two measures. [CheckM] is also to be used as filter for the visual (select to show when [CheckM] is 1)
See it all at work in the attached file.
CheckM = 1 * ( COUNTROWS ( INTERSECT ( DISTINCT ( Table1[New] ), { "Y", "N" } ) ) = 2 )CountM = VAR currentNewID_ = SELECTEDVALUE ( Table1[NewID] ) RETURN SUMX ( FILTER ( ALL ( Table1[Account] ), [CheckM] = 1 ), 1 * ( CALCULATE ( DISTINCT ( Table1[NewID] ) ) = currentNewID_ ) )Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
4 Replies
Anonymous
Create two measures. [CheckM] is also to be used as filter for the visual (select to show when [CheckM] is 1)
See it all at work in the attached file.
CheckM = 1 * ( COUNTROWS ( INTERSECT ( DISTINCT ( Table1[New] ), { "Y", "N" } ) ) = 2 )CountM = VAR currentNewID_ = SELECTEDVALUE ( Table1[NewID] ) RETURN SUMX ( FILTER ( ALL ( Table1[Account] ), [CheckM] = 1 ), 1 * ( CALCULATE ( DISTINCT ( Table1[NewID] ) ) = currentNewID_ ) )Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Hi Anonymous
Place the following M code in a blank query to see the steps of a possible solution
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRigRiU6VYHRjfD843t7BEkYfwEfImpmZQeSMw38jABMq3RJL3g8jHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, New = _t, NewID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", Int64.Type}, {"New", type text}, {"NewID", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Account"}, {{"NewID", each if List.Contains([New], "Y") and List.Contains([New], "N") then [NewID]{0} else null }}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([NewID] <> null)), #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"NewID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Account", each [Account]}}), #"Expanded Account" = Table.ExpandListColumn(#"Grouped Rows1", "Account"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Account",{{"NewID", Int64.Type}, {"Account", Int64.Type}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Account", "NewID", "Count"}) in #"Reordered Columns"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.