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.

Pls help me.

 

  • 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

2 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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