Find duplicates based when another column has two different values

[Anonymous]

Hi all

I have accounts that may be duplicated.

I need to see which accounts have both "Y" and "N" in the 'New' column

I'd like a table that have these duplicate Account numbers.

Many thanks for your time!

[mahoneypat · Accepted solution]

Not sure if you need a DAX table or a measure to use in a table visual (or want this done in your query), but here is a DAX table expression that shows one way to do it.  Just replace "Accounts" with your actual table name.

YesAndNo =
VAR vSummary =
    SUMMARIZE ( Accounts, Accounts[Account], Accounts[New] )
VAR vResult =
    FILTER (
        DISTINCT ( Accounts[Account] ),
        VAR vThisAccount = Accounts[Account]
        RETURN
            SUMX ( FILTER ( vSummary, Accounts[Account] = vThisAccount ), 1 ) = 2
    )
RETURN
    vResult

Pat

[Anonymous]

Hi

Sorry- just a quick extra question:

How do you add the extra column to the YesAndNo table from the Accounts table? :

Accounts table:

YesAndNo table:

[Anonymous]

Fantastic! That is just what I wanted 🙂

Much appreciated