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 (wor...
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

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 ?

andris_'s avatar
andris_
Resolver I
9 years ago

yeah, exactly! group by hasn't even crossed my mind. How is it working in BI? Similar to SQL? 

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    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

    • andris_'s avatar
      andris_
      Resolver I
      9 years ago

      Hey v-huizhn-msft,

       

      Thank you! I think it's working. I've just tested it not with the full datas, but seems great. We're testing it now (and going to test it monday as well I guess), so I'll accept it as a solution when we manage to apply it with the full data table! 

       

      Once again, thank you very much, have a nice weekend!:)

       

      Regards,

      Andris