Need help to Identify Duplicate field

[e116736]

I am trying to find a formula which Identifies the duplicate address and makes it a 1 but keeps the original instance of that address as a 0. 

[e116736]

@Ashish_Mathur Does that example help ?

[e116736]

CustomerID  Customer Address JobType Expected Result

12                    40 Main St             Install           0

13                     11 Water St           Install           0

14                    40 Main St             Verify            1

The goal is to capture the jobs where we went to an address more than one time and exclude those jobs from the totals using the filters on the dashboard

[Ashish_Mathur]

Hi,

This M code works.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Partition = Table.Group(Source, {"Customer Address"}, {{"Partition", each Table.AddIndexColumn(_, "Index",0,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"CustomerID", "JobType", "Index"}, {"CustomerID", "JobType", "Index"})
in
    #"Expanded Partition"

Hope this helps.

[e116736]

Thank you. Is there another version of this formula which can be used on the data view/data table side vs the power Query side. Thank you

[Ashish_Mathur]

Hi,

Use this calculated column formula

Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer Address]=EARLIER(Data[Customer Address])&&Data[CustomerID]<EARLIER(Data[CustomerID])))+0

Hope this helps.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.