Return True when all values are equal to True, otherwise return false

[Anonymous]

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

AllTrue = HASONEVALUE('hf TimeSheet'[Authorised])

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.

[ebeery]

@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())

[Anonymous]

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. 

[ebeery]

@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.

[Anonymous]

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.

[ebeery]

@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?

[Anonymous]

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 🙂

[ebeery]

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 

[Anonymous]

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,

AuthorisedCompleted = IF(SELECTEDVALUE('dim Dates'[DateOnly]) = SELECTEDVALUE(TimeSheet[DTAEnd].[Date]),

IF(SELECTEDVALUE('TimeSheet'[Authorised]) = TRUE(), "Ok", "Unauthorised"),

"Did Not Work").