DAX code: Find duplicates based on another column and count duplicates.

[Anonymous]

This is a follow up to a previous post:

https://community.powerbi.com/t5/Desktop/Find-duplicates-based-when-another-column-has-two-different...

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]

That is fantastic! Thanks a lot.

[AlB · Accepted solution]

@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.

[AlB]

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.

[Anonymous]

That's really appreciated. Unfortunately it has to be in DAX!  

Sorry I should have mentioned that.