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
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.
Some new progress at least... :)
In the query editor, I noticed that the newly created "Full data" column has a button which allows me either to expand or aggregate selected columns. The latter seems to just add counters or summations, but the expand option actually makes selected fields of the original rows (prior to grouping/merging) available in the FIELDS list of the report view -> so by choosing to expand the Date field, I now have it available for a filter!
For some reason, the name expand gave me the impression that I would lose the merging (as in fact the table is shown in data view with all the old rows once again), but instead everything is still shown merged just like I wanted to, as long as I do not include the Full data.Date field into the table visualization. If I do include it, then the table is expanded and the merging is lost, but if I don't included then the table looks OK for my purposes.
I still miss one piece of the puzzle however: expanded fields seem to be automatically converted to string and so no further calculations with them are possible. This is strange, because in the query editor view, I can still see them being recognized as date or numbers. But if I try to use an expanded field in a filter, the filters options are only list and dropdown; and if I try to use them (for example "Full data.Hours") in some calculations, I get an error that the calculation is not allowed on a string.
Any idea on how to use expanded fields with their original data type?