Forum Discussion

pmthu's avatar
pmthu
New Member
4 years ago
Solved

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

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. P...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

rbriga's avatar
rbriga
Icon for Impactful Individual rankImpactful Individual
4 years ago

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

What I'm doing here is:

  1. Adding an "In" and an "Out" columns to flag the action of the row.
  2. 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.
  3. 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