Forum Discussion
Find duplicates based when another column has two different values
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!
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
vResultPat
3 Replies
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
vResultPat