Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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])
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.
So you want to do a group by at day level? and only those records to be considered where work_hrs >1 ?
yeah, exactly! group by hasn't even crossed my mind. How is it working in BI? Similar to SQL?
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |