Forum Discussion
Merge rows by column value depending on date filter
Hi Väinämöinen,
I have tried two solutions, although they can't completely meet your requirement, you can refer to them:
1. Don't create additional date table, use dax like this:
Merge Same Employee = CALCULATE(SUM(Table1[Hours]), ALLSELECTED(Table1[Date]), ALLEXCEPT(Table1, Table1[Employee]))
But when select value in a slicer or report level filter, can't dynamic sum up part of tables by employee name because of allexcept will clear all outside filters.
2. Create additional table Date[Date] using dax like this:
Merge Same Employee = CALCULATE(SUM(Table1[Hours]), CALCULATETABLE(Table1, ALLSELECTED('Date'[Date])), ALLEXCEPT(Table1, Table1[Employee]))In this solution, allexcept take effect row by row because allselect is implemented row by row. So allexcept takes no effect on sum function.
I'm afraid it's difficult to achieve this requirement but I will update here if I have come up with other solutions.
Regards,
Jimmy Tao
Thank you for your help, but I can't seem to apply this to my table. I don't think I really understand how Power BI works.
I have tried to read this article as well as the following video tutorial on grouping rows:
https://docs.microsoft.com/en-us/power-bi/desktop-common-query-tasks#group-rows
https://www.youtube.com/watch?v=nJ7LzwiSwnw
However, both of these explain how to achieve rows grouping by editing the query. I do not want to modify the query, because I am quite sure that I won't be able to show and apply filters in the report itself once the rows are already merged at the query level.
I tried to create a second table based on the original one, and have the rows merged in the second table only. My idea was to have filters on the report* apply on the first table, and then have the second table be re-calculated dynamically from the first whenever the filters are changed. But I cannot figure out if it is possible at all to do any row grouping on a table, because the "Group By" option appears in the query editor, not in the data view.
*so basically the report itself should show only the second table (with merged rows) plus the date filter
I had another idea. Not sure if I am following the right direction, but now I am trying the following approach...
1) Query editor > Group by (Advanced)
2) Select "Group by" = Employee
3) Add an aggregation "Total hours" with operation = Sum, and column = Hours
This now shows the table as it should be, according to my requirements, i.e. one row per employee with the total hours being shown.
The I tried the following:
4) Add another aggregation "Full data" with operation = All Rows
Now in the query editor I can see that there is a new column called "Full data", which contains a value called "Table". For a specific row, this Table value is basically a subtable (of the original table) made with all the old rows that were merged into this new row. This means that all the date information (one per each of the old rows) is still here!
So now my idea was to try and have a date filter in the report target the date inside this "Full data" table.
The problem is... how do I select that? While the "Full data" column is visible in the query editor, it is not visible/selectable in the "FIELDS" pane of the report view!
Hi Väinämöinen,
Your direction is right, use group by can take effect. Have you click close&applied after your operation in query editor?
Regards,
Jimmy Tao
Thank you Jimmy Tao.
Yes, I "closed and applied". The problem is now this:
- in the query editor, after merging the rows I can still the original rows individually if I click on of the "Table" values of the new column "Full data" > this gave me hope that I can still use the "date" information inside this subtable as a filtering variable
- in the data view unfortunately I do not see the "Full data" column at all, and neither in the "FIELDS" pane to the right, so I cannot select for example something like "Full data"."Date" with the filter
This problem is kind of blowing up our entire project, because the filter by date (using date from the original rows by work task) and the merging of rows (i.e. merging all tasks done by the same person) are both principal requirements. This small project is a kind of pilot for our internal use of PowerBI for business reports. Stakeholders in this projects may consider opting out of PowerBI if we cannot make this work.
