Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Team,
I have been working on a report which shows the status of timesheet approvals via departments and employees.
This is the current report look and feel
Overall, I am using a current Measure called AllTrue, however it is only display False across all of my Data.
Measure DAX
TABLE RELATIONSHIPS
TimeSheet Data
TimesheetID | EmployeeID | DepartmentID | ShiftTypeID | DTAStart | DTAEnd | DTStart | Authorised | AuthorisedAT | AuthorisedID | DTRStart | DTREnd | Actual Hours |
54695 | 364 | 6 | 28 | ######## | ######## | FALSE | ######## | 388 | 8.5 | |||
53736 | 173 | 6 | 28 | ######## | ######## | TRUE | ######## | 198 | 7.5 | |||
52540 | 481 | 6 | 28 | ######## | ######## | TRUE | ######## | 362 | 6.25 | |||
51329 | 115 | 6 | 28 | ######## | ######## | TRUE | ######## | 198 | 9 | |||
46628 | 173 | 6 | 28 | ######## | ######## | TRUE | ######## | 198 | 7.5 | |||
45914 | 115 | 6 | 28 | ######## | ######## | TRUE | ######## | 198 | 8 | |||
43790 | 382 | 6 | 28 | ######## | ######## | TRUE | ######## | 350 | 8.5 | |||
40858 | 173 | 8 | 28 | ######## | ######## | TRUE | ######## | 198 | 8.5 | |||
38260 | 115 | 8 | 28 | ######## | ######## | TRUE | ######## | 198 | 9 | |||
38125 | 134 | 8 | 28 | ######## | ######## | TRUE | ######## | 198 | 8.5 | |||
33830 | 412 | 8 | 28 | ######## | ######## | TRUE | ######## | 350 | 8.5 | |||
33356 | 25 | 8 | 28 | ######## | ######## | TRUE | ######## | 388 | 7.5 | |||
29124 | 173 | 8 | 28 | ######## | ######## | TRUE | ######## | 198 | 7.5 | |||
28753 | 134 | 8 | 28 | ######## | ######## | TRUE | ######## | 198 | 9 | |||
27017 | 115 | 6 | 28 | ######## | ######## | TRUE | ######## | 198 | 8.5 | |||
23406 | 134 | 6 | 28 | ######## | ######## | TRUE | ######## | 198 | 6.25 | |||
22642 | 173 | 6 | 28 | ######## | ######## | TRUE | ######## | 198 | 8.5 | |||
22540 | 12 | 6 | 28 | ######## | ######## | FALSE | ######## | 388 | 7.5 |
The other tables are simply tables which relate an ID back to an friendly name along with generic Dimension Date Table.
Hoping someone might be able to provide some guidance on what I need to do achieve this, In python my brain would go loop over a filter rows but not sure how BI would handle that.
@Anonymous also I would just point out that the HASONEVALUE() method is probably not quite optimally precise, if what you truly care about is whether the values are all TRUE. In a scenario where all values are FALSE I think that measure would be returning TRUE as well.
Something like below might be more exact:
IF(SELECTEDVALUE('hf TimeSheet'[Authorised]) = TRUE(), TRUE(), FALSE())
I did attempt that Measure, and the results are below. All timesheets have been approved for this past date. Yet it still seems to throw a False.
@Anonymous your current approach seems sound, so there must be something I'm missing.
As a troubleshooting step, could you create a table visual filtered to a with a single department and day, showing the values of [Authorized]?
That might help us determine where the HASONEVALUE() approach is failing.
Hi @ebeery ,
I've created quick filter some data. Selected 1 Department and 1 Date. Output is this. Where 1 employee has had their timesheet approved, the other havn't yet.
@Anonymous ok, but based on this data you're showing here the expected result for this department and day context in your matrix would be FALSE (because [Authorised] contains more than a single distinct value). Do you have a specific example where the measure should be returning TRUE but isn't?
Hey mate, using your Measure, it looks to be working when department has a single Employee.
This is from lastweeks, so every field should be true due approval has to happen before payroll.
I have a feeling its something to do with the ROW contexting BI does, but this is now into realm beyond my technical knowledge of BI 🙂
Yeah, everything you're describing seems to be the expected behavior, so I'm not really understanding what you're hoping to see differently in your use case.
I would recommend doing some reading/watching on the concept of "filter context" within Power BI. Here's one (of many) good resources on the topic.
DAX for Power BI - Understanding Filter Context
Hi Eberry, I think I have isolated what is causing the incorrect data I am seeing.
I believe, what is happening, because I am displaying a Matrix of Columns of dates. When an Employee hasn't worked that day, they automatically being displayed and returning a false.
I believe, I need to write a Filter on Names, which would only return people who worked during that payweek.
Looking at the filter command, I can't see how i can filter via date. Any advice?
I was thinking something like,
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
90 | |
35 | |
35 |
User | Count |
---|---|
154 | |
102 | |
82 | |
64 | |
54 |