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_

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 ?

    • 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