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.
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
Solved! Go to 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]))
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?
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]))
Hi Xue Ding,
Thank you for your reply. I have already solved the issue. Appreciate.
Regards.
Jenas
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 |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |