Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

 

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

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.

Need Help

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

       

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.