Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
I'm looking to create a measure that counts the number of days a worker has worked in a period. This would be easy enough but the data is set up so that if a worker does enough hours to get overtime, there will be a new line for each over time rate as below:
Worker | Date | Rate | Hours |
Tom | 23/06/2022 | 1.5x | 1 |
Tom | 23/06/2022 | 1x | 4 |
John | 23/06/2022 | 1x | 8 |
John | 23/06/2022 | 1.5x | 2 |
John | 21/06/2022 | 1x | 6 |
John | 22/06/2022 | 1x | 7 |
In this example Tom would have worked 1 day and John 3 days.
The end goal is working out the average number of days worked over a period, so in this example the average days worked would be 2 days. We could then track this over the entire workforce in a line graph for example.
Is this possible in DAX?
@Anonymous , Try like
averagex(Values(Table[Worker]), calculate( Countrows(Summarize(Table, Table[Worker], Table[Date]))))
User | Count |
---|---|
83 | |
70 | |
70 | |
66 | |
53 |
User | Count |
---|---|
94 | |
92 | |
91 | |
77 | |
70 |