Unique count

[Anonymous]

ID	Desired Output
701	1
701	0
703	1
702	1
702	0
703	0
702	0

Need Help to find out duplicate

[ryan_mayu]

@Anonymous 

If we understanding is correct, please try below steps.

1. create an index column.

2. try to create a column

Column = 
VAR _count= CALCULATE(COUNTROWS(Sheet7),FILTER(Sheet7,Sheet7[ID]=EARLIER(Sheet7[ID])&&Sheet7[Index]>=EARLIER(Sheet7[Index])))
return if(_count>1,1,0)

Hope this is helpful.

[ryan_mayu]

@Anonymous 

could please explain the logic of the output?

if found duplicated ,then output 1?

Should the second 702 be 1 because the last line is also 702?

ID	Desired Output
701	1
701	0
703	1
702	1
702	1
703	0
702	0

[Anonymous]

ID	Desired Output
701	1
701	0
703	1
702	1
702	0
703	0
702	0

If id occuring 2nd time  or more than 1st time than output to be 0 

[dax]

Hi @Anonymous ,

You also could try below M code 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjcwVIrVQaaNobQRGo0kHgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"all", each _, type table [ID=number, Desired Output=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all],"index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"index"}, {"index"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",each [index],each if [index]=1 then 1 else 0,Replacer.ReplaceValue,{"index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"all"})
in
    #"Removed Columns"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@Anonymous 

please try below formula.

Column = 
VAR _count= CALCULATE(COUNTROWS(Sheet7),FILTER(Sheet7,Sheet7[ID]=EARLIER(Sheet7[ID])&&Sheet7[Index]>=EARLIER(Sheet7[Index])))
VAR _count2=CALCULATE(COUNTROWS(Sheet7),FILTER(Sheet7,Sheet7[ID]=EARLIER(Sheet7[ID])))
return if(_count=_count2,1,0)