Duplicate IDs with criteria

deannapi — Wed, 27 Mar 2024 18:53:25 GMT

I have a table with an ID column, a column "Origin" and a date column.

ID	Origin	Date
12	Web	1/1/2024
12	Other	1/2/2024
13	Other	1/2/2024
14	Web	1/3/2024
14	Other	1/3/2024
15	Other	1/3/2024
15	Web	1/4/2024
15	Other	1/4/2024
16	Other	1/4/2024
17	Web	1/4/2024
17	Web	1/5/2024
18	Web	1/5/2024
18	Other	1/5/2024

If an ID is repeated in one day and one of the origins = "Other", then I want that ID to be "Other", not "web". Basically, "Other" trumps "Web" if an ID belongs to both in the same day.

How can get a table to reflect this via DAX, not power query? I have no code, I don't know what to even try.

Re: Duplicate IDs with criteria

adudani — Wed, 27 Mar 2024 20:55:41 GMT

hi deannapi ,

create a blank query and copy paste the below code into the advanced editor

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRS0lEKT00Ckob6hvpGBkYmSrE6UHH/kozUIrCMEZKMMU4ZEySzjNHEETqQZUzxysDMMsGpA1nGDKeMOQ6zkMVNkcQt8IgjbIDJxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Origin = _t, Date = _t]), //merge date and id columns #"Date-ID concatenation" = Table.AddColumn(Source, "Date -ID", each Text.Combine({[Date], [ID]}, " - "), type text), #"Changed Type" = Table.TransformColumnTypes(#"Date-ID concatenation",{{"ID", Int64.Type}, {"Origin", type text}, {"Date", type date}}), // group all data by date and id column #"Grouped Rows" = Table.Group(#"Changed Type", {"Date -ID"}, {{"Data", each _, type table [ID=nullable number, Origin=nullable text, Date=nullable date, #"Date -ID"=text]}}), // criteria to check if multiple ids in the same day ReplaceID = Table.AddColumn(#"Grouped Rows", "ReplaceID", each not( List.Count([Data][ID]) = List.Count(List.Distinct([Data][ID])))), // Get data which doesn't need to be replaced. Also, replace Origin that has multiple ids in same day Data_New = Table.AddColumn(ReplaceID, "Data_New", each if [ReplaceID]= true then Table.AddColumn(Table.RemoveColumns([Data],"Origin"),"Origin", each "Other") else [Data]), #"Removed Other Columns" = Table.SelectColumns(Data_New,{"Data_New"}), #"Expanded Data_New" = Table.ExpandTableColumn(#"Removed Other Columns", "Data_New", {"ID", "Origin", "Date"}, {"ID", "Origin", "Date"}) in #"Expanded Data_New"

tried to solve this via Power Query. let me know if this is the expected output below:

steps:

1. created a merged date-id column

2. group all data by this column

3. applied a check to see if there are multiple ids on that date-id combination

4. if yes, replace "Web" with "Other", else give the Data without replacement.

5. expanded the "New_Data" Table with the changes.

Re: Duplicate IDs with criteria

tamerj1 — Wed, 27 Mar 2024 21:29:47 GMT

Hi deannapi

for a DAX solution you my try the following calculated table

NewTable =
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[Date] ),
"Origin", CALCULATE ( MIN ( 'Table'[Origin] ) )
)

topic Re: Duplicate IDs with criteria in DAX Commands and Tips

Duplicate IDs with criteria

Re: Duplicate IDs with criteria

Re: Duplicate IDs with criteria