cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Group By Date Hour and Production line

Hi All,

 

Appreciate if you guys can help. 

 

I have created a column 'NewDateTime' from my datetime column to show the date and hour group.

 

jenas6423_0-1600675480014.png

 

I would like to group by Prodline, Date & Hour. With Actual and Incremental value is from the max value - min value within the hour.

 

The expected result is like:

 

jenas6423_1-1600675870402.png

 

You may find the data source file at below link:

https://drive.google.com/file/d/10e__qMyssLSVOEnSfhdRBRG4EG5cIrZ2/view?usp=sharing

 

I couldnt use power query as Im using direct query, so have to create new column with dax.

 

Regards.

 

Jenas

 

 

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @jenas6423 ,

 

You could try to use ALLEXCEPT to group data. But for your expected results, I'm a little confused. Do you want to create a "Calculation" column that is grouped by Prodline, Date & Hour? If so, there is no data about Max and Min values in your data source.

 

I created two measures to calculate actual and incremental values by group. Create calculated columns with Direct Query may change your connection mode from DQ to mix (DQ+Import). So it is best to create measures to implement it.

actual_ = CALCULATE(SUM(data[actual_val]),ALLEXCEPT(data,data[Prodline],data[NewDateTime]))
incremental_val_ = CALCULATE(SUM(data[incremental_val]),ALLEXCEPT(data,data[Prodline],data[NewDateTime]))

 3.PNG

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Community Support
Community Support

Hi @jenas6423 ,

 

You could try to use ALLEXCEPT to group data. But for your expected results, I'm a little confused. Do you want to create a "Calculation" column that is grouped by Prodline, Date & Hour? If so, there is no data about Max and Min values in your data source.

 

I created two measures to calculate actual and incremental values by group. Create calculated columns with Direct Query may change your connection mode from DQ to mix (DQ+Import). So it is best to create measures to implement it.

actual_ = CALCULATE(SUM(data[actual_val]),ALLEXCEPT(data,data[Prodline],data[NewDateTime]))
incremental_val_ = CALCULATE(SUM(data[incremental_val]),ALLEXCEPT(data,data[Prodline],data[NewDateTime]))

 3.PNG

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi Xue Ding,

 

Thank you for your reply. I have already solved the issue. Appreciate.

 

Regards.

 

Jenas

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors