DAX Identifying Duplicated Values

[PMF998]

Dear All,

I have a PowerPivot table below.

You will see for Meter Point 1012351309356, with Filter  "Consumptio" there is a duplicated Invoice_End at row 5 & 6 (10/05/2018). I would like to be able to flag the 1st and subsequent duplicated values in column Dup so I can treat values in that row differenctly in subsequent calcualtions.

Can you help?

Many thanks in advance!

Meter Point	Filter	Invoice_End	Dup
1012351309356	Cancellati	10/05/2018
1012351309356	Closing ba	26/10/2018
1012351309356	Closing ba	28/08/2018
1012351309356	Consumptio	13/02/2018
1012351309356	Consumptio	10/05/2018
1012351309356	Consumptio	10/05/2018	1st Dup
1012351309356	Consumptio	07/08/2018
1012351309356	Consumptio	30/09/2018

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Meter Point]=EARLIER(Data[Meter Point])&&Data[Filter]=EARLIER(Data[Filter])&&Data[Invoice_End]=EARLIER(Data[Invoice_End])))>1,"Dup",BLANK())

Hope this helps.

[PMF998]

Hi Ashish,

This is great thanks & works fine.

Is it possible to add an extra step that does not flag the first instance of the duplicate (let's call this the original value) only any subsequent duplicates? 

This is because I want all the data from the row containing the original value, but only some of the data from the subsequent rows containing duplicated values.

Many thanks 

Peter

[Ashish_Mathur · Accepted solution]

Hi,

This M code works fine

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Meter Point", Int64.Type}, {"Filter", type text}, {"Invoice_End", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.ToText([Meter Point])&[Filter]&Date.ToText([Invoice_End])),
    Partition = Table.Group(#"Added Custom", {"Custom"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Meter Point", "Filter", "Invoice_End", "Index"}, {"Meter Point", "Filter", "Invoice_End", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Partition",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Dup", each if [Index] = 2 then "Dup" else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns1", {{"Invoice_End", type date}}, "en-IN")
in
    #"Changed Type with Locale"

Hope this helps.

[Anonymous]

This is a difficult problem to solve in DAX. Doing it in the query editor is far easier is that an option for you?

[PMF998]

Thanks Thomas.

How would I do it in query editor?