Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey everyone,
I am running into a issue where I have 2 set of data, one is capturing individual time entry in a format like
User | Date | Hours | Type of Hours
and then I have a table that is a date table which is like
Date | Day | Month | Year | Is Holiday? | Is Workday? | Expected Hours |
They are related by date. I have made these two tables to try to capture the expected "Workdays" or "Hours worked" for a date range selected. In the image below I have selected a one week period, which a measure is summarizing into 40 hours which is correct. But when I try to apply it to the user table, it is trying to count the days that the users have time entered and summarizing the "Expected Hours" only for that day. Where I would like it to simply use the 40 hours expected based on the measure.
How can I have the Date Table & the Time Table related by date, but have the measure only related to the date slicer selected and not the users entries?
@wyattexplore you should change your "total expected hours" measure like this
Total Expected Hours = CALCULATE( SUM( Date[Expected Hours] ), ALLSELETED( Date ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hey Parry,
Thanks for replying. I tried your version of the measure, but it still is returning the same. I believe this is due to the following:
Table A lists only time enteries that have been entered, but not blanks for days that they weren't. When referencing the Date table it sees only those days and returns with a summarization of those hours specifically.
I.E: Bill works
Hours | Date
8 | Mon
8 | Tues
8 | Wed
8 | Thurs
The Date table references those days, based on the relationship, and returns that the [Workday Hours] Column returns with 32.
I am try to have it return based on the date slicer I've selected, not the user; But I still need to use that to calculate per user their utilization. Is there a way to use the measure to calculate, with out have the relationship with the user come into effect? Is there another way to do this?
Is there a way that it calculate it, even if the other table shows a blank for that day?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |