topic Re: How to find duplicated data in first column based on second column and show result as Status in DAX Commands and Tips

How to find duplicated data in first column based on second column and show result as Status

pmthu — Tue, 23 Nov 2021 06:09:39 GMT

Hi All,

I have one thing to solve.I want to add new column as "Complete or Not" deponding on Action Column.If one date have sign_in and sign_out,data in new column is "Complete" for that date.

Pls help me.

Re: How to find duplicated data in first column based on second column and show result as Status

rbriga — Tue, 23 Nov 2021 06:41:44 GMT

I would take it to Power Query to prepare the data for this.

What I'm doing here is:

Adding an "In" and an "Out" columns to flag the action of the row.
Grouping By Name and Date to get a single row for each Name+Date combination; "In" and "Out" would show 1 if there was a sign-in and sign-out for this combination, respectively.
Adding a column that checks if, for each Name+Date combination, there were both a sign-in and sign-out; if so, "Complete".

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employer Name", type text}, {"date", type datetime}, {"Action", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "In", each if [Action] = "sign_in" then 1 else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Out", each if [Action] = "sign_out" then 1 else null),
#"Grouped Rows" = Table.Group(#"Added Conditional Column1", {"Employer Name", "date"}, {{"Signed In", each List.Max([In]), type nullable number}, {"Signed Out", each List.Max([Out]), type nullable number}}),
Complete = Table.AddColumn(#"Grouped Rows", "Complete", each if [Signed In] = 1 and [Signed Out] = 1
then "Complete"
else "Incomplete")
in
Complete

Re: How to find duplicated data in first column based on second column and show result as Status

Anonymous — Fri, 26 Nov 2021 08:16:50 GMT

Hi pmthu ,

Try this DAX formula.

Column = var _distinct = CALCULATE(DISTINCTCOUNT('Table'[action]),filter(ALLEXCEPT('Table','Table'[date]),'Table'[action]<>BLANK())) return IF(_distinct=2,"complete","not")

Best Regards,

Jay