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

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