Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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
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
I would take it to Power Query to prepare the data for this.
What I'm doing here is:
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
22 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
32 | |
23 | |
22 | |
22 |