Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Anonymous
Not applicable

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

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

brytonwishart_2-1628213470775.png

 

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

brytonwishart_0-1628213430159.png

 

TimeSheet Data

TimesheetIDEmployeeIDDepartmentIDShiftTypeIDDTAStartDTAEndDTStartAuthorisedAuthorisedATAuthorisedIDDTRStartDTREndActual Hours
54695364628################ FALSE########388  8.5
53736173628################ TRUE########198  7.5
52540481628################ TRUE########362  6.25
51329115628################ TRUE########198  9
46628173628################ TRUE########198  7.5
45914115628################ TRUE########198  8
43790382628################ TRUE########350  8.5
40858173828################ TRUE########198  8.5
38260115828################ TRUE########198  9
38125134828################ TRUE########198  8.5
33830412828################ TRUE########350  8.5
3335625828################ TRUE########388  7.5
29124173828################ TRUE########198  7.5
28753134828################ TRUE########198  9
27017115628################ TRUE########198  8.5
23406134628################ TRUE########198  6.25
22642173628################ TRUE########198  8.5
2254012628################ 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.

8 REPLIES 8
ebeery
Solution Sage
Solution Sage

@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
Not applicable

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. 

brytonwishart_0-1628215625386.png

 

ebeery
Solution Sage
Solution Sage

@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
Not applicable

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.

brytonwishart_0-1628215451779.png

 

@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
Not applicable

Hey mate, using your Measure, it looks to be working when department has a single Employee.

brytonwishart_2-1628217410410.png

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 


Anonymous
Not applicable

Hi Eberry, I think I have isolated what is causing the incorrect data I am seeing.

brytonwishart_0-1628377876428.png

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").

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.