Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi there
I have three tables. A basic one with employees, aka
Employee |
Aileen |
Tom |
Barbara |
Another one with their workoing hours per day (multiple entries per day are possible)
Employee | Date | working hours |
Aileen | 2019/01/05 | 5 |
Aileen | 2019/01/05 | 3 |
Aileen | 2019/01/06 | 8 |
Aileen | 2019/01/08 | 6 |
Tom | 2019/01/05 | 1 |
Tom | 2019/01/06 | 2 |
Tom | 2019/01/08 | 3 |
Barbara | 2019/01/05 | 3 |
Barbara | 2019/01/06 | 8 |
Barbara | 2019/01/08 | 6 |
And a third one with a working period (from..to). This table is unrelated to the others
Period | From | To |
Period 01 | 2019/01/01 | 2019/01/06 |
Period 02 | 2019/01/07 | 2019/01/13 |
Period 03 | 2019/01/14 | 2019/01/20 |
I want to add a measure to the third one which includes the SUM of working hours of the employees in each period. In a dropdown I then put the employees. If no employee is selected I want to see the SUM of all working hours. If only one/some are selected, I want to see only the selected employees working hours.
I hope you can help me
Thanks zemi
Solved! Go to Solution.
Hi @Anonymous ,
Create the following measure:
Hours = CALCULATE ( SUM ( WorkingHours[working hours] ); FILTER ( ALL ( WorkingHours[Date] ); WorkingHours[Date] >= SELECTEDVALUE ( Periods[From] ) && WorkingHours[Date] <= SELECTEDVALUE ( Periods[To] ) ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey @Anonymous ,
this is a slightly different solution than the one @MFelix provided.
Please be aware that my solution also requires a calendar tarble (though it does not have to be related to one of the tables).
Create this measure:
Working Hours = SUMX( VALUES(Period) ,CALCULATE( SUM(Working[working hours]), TREATAS(DATESBETWEEN('Calendar'[Date],'Period'[From],'Period'[To]),Working[Date]) ) )
Regards,
Tom
Hi @Anonymous ,
Create the following measure:
Hours = CALCULATE ( SUM ( WorkingHours[working hours] ); FILTER ( ALL ( WorkingHours[Date] ); WorkingHours[Date] >= SELECTEDVALUE ( Periods[From] ) && WorkingHours[Date] <= SELECTEDVALUE ( Periods[To] ) ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Works nice. Thanks.
If I put the measure in a table, I do not get the sum of all rows. Why?
Regrads, zemi
Hey @Anonymous ,
this is a slightly different solution than the one @MFelix provided.
Please be aware that my solution also requires a calendar tarble (though it does not have to be related to one of the tables).
Create this measure:
Working Hours = SUMX( VALUES(Period) ,CALCULATE( SUM(Working[working hours]), TREATAS(DATESBETWEEN('Calendar'[Date],'Period'[From],'Period'[To]),Working[Date]) ) )
Regards,
Tom
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
213 | |
89 | |
77 | |
66 | |
60 |