Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pmthu
New Member

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.

pmthu_0-1637647527965.png

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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")

Capture.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

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")

Capture.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
rbriga
Impactful Individual
Impactful Individual

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

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.