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
Anonymous
Not applicable

Calculate distinct number of hours worked per day per person

Hi all, 
For work I am trying to figure out how to create to correct measurement. 
The dataset consist of all the completed phone calls per person, per date, per hour. From this table I want to be able to get number of completed phonescalls per person per day AND the distinct hours worked per person per day.

 And if I put a slicer on it, I want to be able to filter on multiple days in order to get the total numers of calls completed and total hours worked.  Below i added an example of the dataset. 

So at the end I want to have a pivot with  "Total calls completed"  and "Total hours worked
And two slicers: date and person (able to filter on multiple items.

If I filter on "Jan" and "1-1-2020" <-- I need to get: 3 calls AND 2 hours
If I filter on "Jan" and "1-1-2020 + 2-1-2020" <-- I need to get: 9 calls and 5 hours 

Hope you can help me out :)!

PersonDateTime

Jan1-1-20201
Jan1-1-20202
Jan1-1-20202
Piet1-1-202012
Piet1-1-202013
Piet1-1-202012
Jan2-2-20203
Jan2-2-20203
Jan2-2-20203
Jan2-2-20203
Jan2-2-20201
Jan2-2-20202
Piet2-2-202011
Piet2-2-202012
Piet2-2-202013
Piet2-2-202014
Piet2-2-202015
Piet2-2-202016
Piet2-2-202017
Piet2-2-202017
Piet2-2-202017
Piet2-2-202017
3 REPLIES 3
amitchandak
Super User
Super User

@Anonymous , Hours seem  like max not distinct

 

Calls = countrows(Table)
hours = sumx(summarize(Table, Table[person], Table[Date], "_1", max(Table[Time])), [_1])

wdx223_Daniel
Super User
Super User

@Anonymous 

Total_Calls:=COUNTROWS('Table2')

Total_Hours:=COUNTROWS(DISTINCT('Table2'))

speedramps
Super User
Super User

Please consider this solution and leave kudos ....

 

Click here to download PBIX example 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.