Need to tag Original duplicate in dax function

[balajir98]

Need to tag Original duplicate in dax function, example below, thanks in advance.

[Fowmy · Accepted solution]

@balajir98 

I cannot think of a solution in DAX for this problem.
You need a supporting column that identifies the order/sort, I added an index column and completed it in Power Query itself. 

Paste the below code on a new blank query in the Advanced Editor and see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLSNTDVNTJU0lEyNFCK1aGiiDFcxIjKIiZwEWOyRWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}},"en-gb"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Count", each _, type table [Date=nullable date, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1) ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "Value", "Index"}, {"Date", "Value", "Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Index] = 1 then "Original" else "Duplicate"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Index"})
in
    #"Removed Columns"

[v-angzheng-msft · Accepted solution]

Hi, @balajir98 

Add a index column in Power Query, then try to create a measure like below:

_Tag =
IF (
    CALCULATE (
        COUNT ( 'Table'[Date] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Value] ),
            [Index] <= MAX ( [Index] )
        )
    ) = 1,
    "Original",
    "Duplicate"
)

Result:

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy · Accepted solution]

@balajir98 

I cannot think of a solution in DAX for this problem.
You need a supporting column that identifies the order/sort, I added an index column and completed it in Power Query itself. 

Paste the below code on a new blank query in the Advanced Editor and see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLSNTDVNTJU0lEyNFCK1aGiiDFcxIjKIiZwEWOyRWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}},"en-gb"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Count", each _, type table [Date=nullable date, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1) ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "Value", "Index"}, {"Date", "Value", "Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Index] = 1 then "Original" else "Duplicate"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Index"})
in
    #"Removed Columns"