filtering days

[andris_]

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

[vanessa]

@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_]

@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]

@andris_

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

[andris_]

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

[v-huizhn-msft · Accepted solution]

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_]

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