calculating number of days two tech work on the same job

[MarcF142]

hi 

I am trying to find the number of days two techs or more is working on the same job.

I try Pivot Table and =Countifs I can not seem to make it work.

Does anyone have an idea?

regards

Job #             Tech name            day of work

20593	Tony	28-Jan-21
20593	Paul	28-Jan-21
20593	Tony	29-Jan-21
20593	Paul	29-Jan-21
20906	Byron	19-Jan-21
20906	Jason	19-Jan-21
20906	Byron	20-Jan-21
20906	Jason	20-Jan-21
20906	Byron	21-Jan-21
20906	Jason	21-Jan-21
20906	Byron	22-Jan-21
20906	Jason	22-Jan-21
20947	SANDOR	5-Jan-21
20947	Brayden	19-Jan-21
20947	Tony	19-Jan-21
20947	Andy	20-Jan-21
20947	Tony	20-Jan-21
20947	Brayden	22-Jan-21
20947	SANDOR	22-Jan-21
20947	Andy	22-Jan-21
20995	Andy	25-Jan-21
20995	Guy	25-Jan-21

[wdx223_Daniel · Accepted solution]

let
Source = Excel.Workbook(File.Contents("C:\Users\johndoe\Documents\John Changes Sept 2021\2021 FM report of data\test data for blog.xlsx"), null, true),
Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"hDocketNumber", Int64.Type}, {"First Name", type text}, {"date_of_report", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"hDocketNumber", "Job #"}, {"First Name", "Tech name"}, {"date_of_report", "day of work"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Tech name", type text}, {"day of work", type date}}),
Custom1 = Table.Group(#"Changed Type1",{"Job #","day of work"},{"Tech Count",each List.Count(List.Distinct([Tech name]))}),
Custom2 = Table.Group(Custom1,"Tech Count",{"days",Table.RowCount})
in
Custom2

[v-angzheng-msft]

Hi, @MarcF142 

May I ask if your problem has been solved? Is the above post helpful to you?

If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01 · Accepted solution]

@MarcF142  achievable in DAX with this measure

Measure = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[date] ),
    ALLEXCEPT ( 'Table', 'Table'[job], 'Table'[name] )
)

[wdx223_Daniel]

let
    Source = Table.TransformColumns(Excel.CurrentWorkbook(){[Name="Table2"]}[Content],{"day of work",each Date.From(_,"en-us")}),
    Custom1 = Table.Group(Source,{"Job #","day of work"},{"Tech Count",each List.Count(List.Distinct([Tech name]))}),
    Custom2 = Table.Group(Custom1,"Tech Count",{"days",Table.RowCount})
in
    Custom2

[MarcF142]

thank you, wdx223_Daniel

the answer is really what I was looking for, however, I cant seem to make it work the same as you.

the advance editor sees no error.

let
Source = Excel.Workbook(File.Contents("C:\Users\johndoe\Documents\John Changes Sept 2021\2021 FM report of data\test data for blog.xlsx"), null, true),
Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"hDocketNumber", Int64.Type}, {"First Name", type text}, {"date_of_report", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"hDocketNumber", "Job #"}, {"First Name", "Tech name"}, {"date_of_report", "day of work"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Tech name", Int64.Type}, {"day of work", type date}}),
Custom1 = Table2.Group(Source,{"Job #","day of work"},{"Tech Count",each List.Count(List.Distinct([Tech name]))}),
Custom2 = Table2.Group(Custom1,"Tech Count",{"days",Table.RowCount})
in
#"Changed Type1"

I know my source file is different but this should not be a problem.

I see the Table load window open and it flashes Data updated but it never opens in my Workbook.

instead, I get this error message.

 I don't see how "Table2.Group" wasn't recognized if the editor see no error?

any idea what I a doing wrong?

[wdx223_Daniel · Accepted solution]

let
Source = Excel.Workbook(File.Contents("C:\Users\johndoe\Documents\John Changes Sept 2021\2021 FM report of data\test data for blog.xlsx"), null, true),
Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"hDocketNumber", Int64.Type}, {"First Name", type text}, {"date_of_report", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"hDocketNumber", "Job #"}, {"First Name", "Tech name"}, {"date_of_report", "day of work"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Tech name", type text}, {"day of work", type date}}),
Custom1 = Table.Group(#"Changed Type1",{"Job #","day of work"},{"Tech Count",each List.Count(List.Distinct([Tech name]))}),
Custom2 = Table.Group(Custom1,"Tech Count",{"days",Table.RowCount})
in
Custom2

[askhanduja]

Hi,

Please see solution file here.

The solution looks like this:

If this solves your problem please consider marking this post as the solution so that others with a similar problem may find the answer easily. A thumbs would also be greatly appreciated.