The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Jan | 1-1-2020 | 1 |
Jan | 1-1-2020 | 2 |
Jan | 1-1-2020 | 2 |
Piet | 1-1-2020 | 12 |
Piet | 1-1-2020 | 13 |
Piet | 1-1-2020 | 12 |
Jan | 2-2-2020 | 3 |
Jan | 2-2-2020 | 3 |
Jan | 2-2-2020 | 3 |
Jan | 2-2-2020 | 3 |
Jan | 2-2-2020 | 1 |
Jan | 2-2-2020 | 2 |
Piet | 2-2-2020 | 11 |
Piet | 2-2-2020 | 12 |
Piet | 2-2-2020 | 13 |
Piet | 2-2-2020 | 14 |
Piet | 2-2-2020 | 15 |
Piet | 2-2-2020 | 16 |
Piet | 2-2-2020 | 17 |
Piet | 2-2-2020 | 17 |
Piet | 2-2-2020 | 17 |
Piet | 2-2-2020 | 17 |
@Anonymous , Hours seem like max not distinct
Calls = countrows(Table)
hours = sumx(summarize(Table, Table[person], Table[Date], "_1", max(Table[Time])), [_1])
@Anonymous
Total_Calls:=COUNTROWS('Table2')
Total_Hours:=COUNTROWS(DISTINCT('Table2'))
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
5 |
User | Count |
---|---|
23 | |
13 | |
13 | |
8 | |
8 |