Group By Date Hour and Production line

[jenas6423]

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.

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:

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

[v-xuding-msft · Accepted solution]

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

 

[jenas6423]

Hi Xue Ding,

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

Regards.

Jenas