Group By Date Hour and Production line

[Anonymous]

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

[Syndicate_Admin]

I have two call tables, one contains unique records (table 1) and the other records in sequence (Table 2), I need to find a way to group the call sequence of table 2 with the records of table 1; The critical thing is that the only key I can in both tables is the date and time.

Can someone advise me?

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

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

 

[Anonymous]

Hi Xue Ding,

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

Regards.

Jenas