Forum Discussion

andris_'s avatar
andris_
Resolver I
9 years ago
Solved

filtering days

Hey everyone,

 

I had a post before, but I think I overcomplicated it so I give it a second try and this time I'll make it simple.

 

I'd like to create a measure which aggregates daily datas (work hours) but I'd also like to filter out those days where the values of the work hours are less than 1.

 

Any ideas?

 

Thanks in advance,

Andris

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi andris_,

    For group by in Power BI. You can use ALLEXCEPT filter in formula. Or you can use SUMMARIZE function to create a new table.

    For instance, I have the following sample data.



    1. I use the SUMMARIZE function by click new table->type the following formula. Please see the new table shown in screenshot.

    New table = SUMMARIZE(Test22,Test22[Date],"each day",SUM(Test22[Hours]))




    Then you can use the new table to calculate the aggregated work hours.

    New_Measure= SUM(New table[each day])



    2.  I use the ALLEXCEPT filter, please review the formula and the result in screenshot below.

    each day = CALCULATE(SUM(Test22[Hours]),ALLEXCEPT(Test22,Test22[Date]))




    Then you can calculate the aggregated work hours using similar solution above.

    If you have other issues, don't hesitate to let me know.

    Best Regards,
    Angelia

6 Replies

  • vanessa's avatar
    vanessa
    Post Patron
    9 years ago

    andris_

    You can create a calculated column first with the following syntax:

    New_Column= IF(TableName[Work_Hrs]>1,TableName[Work_Hrs],0)

     

    Then, create a calculated measure with the following syntax:

    New_Measure= SUM(TableName[New_Column])

    • andris_'s avatar
      andris_
      Resolver I
      9 years ago

      vanessa

       

      The problem with this is that we need the aggregated work hours a day, and there are more than 1 values for a day. This one is filtering out every record which is less than 1.

      • vanessa's avatar
        vanessa
        Post Patron
        9 years ago

        andris_

        So you want to do a group by at day level? and only those records to be considered where work_hrs >1 ?