Forum Discussion

hoyty's avatar
hoyty
Regular Visitor
3 years ago

Filter based on a calculation per row?

I feel like what I am trying to do is straightforward and I am missing the way to do it.

 

I have a measure that is:

After 4:00 = COUNTAX(FILTER('Attendance Review','Attendance Review'[SignoutTime] > TIMEVALUE("16:00") && ISBLANK('Attendance Review'[Afternoon Bus])),'Attendance Review'[SignoutTime])
 
What I want to do is have a filter that is essentially:
Is After 4:00 = 'Attendance Review'[SignoutTime] > TIMEVALUE("16:00") && ISBLANK('Attendance Review'[Afternoon Bus])
 
It is sort of a SQL WHERE statement on the data. I just can't figure out where an how to enter this statement to have it work. I could do a filter on the two fields but I can't figure out how to do a time only filter on the time field.
 
Thanks for any advice.
Need Help

4 Replies

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    Can you provide some sample data to work with? PBIX file with sample rows and description what do you expect (example) would be great. 🙂 You can use wetransfer.com to send data/pbix file.

    • hoyty's avatar
      hoyty
      Regular Visitor
      3 years ago

      The data has student names so it is difficult to share. I am pulling from SQL View. I want to filter on one column being flase / null and another that is time being greater than 4:00 PM.

      The final column in the table shows the count that is evaluating the forumla but I want to use that as a filter rather than a count.

       

      Is there a way to enter a filter as a command rather than on a single field?

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    Create a new column in you table:

    After 4:00 = 'Attendance Review'[SignoutTime ] > TIMEVALUE("16:00") && OR(ISBLANK('Attendance Review'[Afternoon Bus ]),'Attendance Review'[Afternoon Bus ]="")

     

    Then you can use it as a filter. Note that using IF statement you can return the values that you want on filter.

     

     

     

    • hoyty's avatar
      hoyty
      Regular Visitor
      3 years ago

      Thanks, that didn't work either. I think my issue is my table is a summary / count table and I am trying to filter on individual items rather than the counts. My solution for now is to edit the query

       

      #"dbo_Attendance Review" = Source{[Schema="dbo",Item="Attendance Review"]}[Data],
      #"Filtered Rows" = Table.SelectRows(#"dbo_Attendance Review", each [SignoutTime] > #time(16, 0, 0))

       

      It isn't as flexible but gets the job done.