Merge rows by column value depending on date filter

[Väinämöinen]

I have a single table from a query to an external source. Each row represents working hours done by a certain employee on a given date:

ID    Employee  Hours   Date
---------------------------------------------
1     John      2       1.1.2018
2     Mary      5       12.1.2018
3     Sue       4       4.2.2018
4     Mary      3       20.2.2018
5     John      1       25.2.2018
6     Bob       6       3.3.2018

I would like to DISPLAY the table above in such a way that all rows with the same employee are MERGED together and show the TOTAL hours:

Employee  Hours
---------------------------------------------
John      3 
Mary      8 
Sue       4
Bob       6 

BUT, I want to be able to have a FILTER BY DATE that changes which rows are actually selected. For example, if a consumer of the report selects the period between 1.2.2018 and 28.2.2018 on the filter (i.e. February only), I want the table in the report to show the following, which is basically the same kind of MERGE but applied only to all rows that match the FILTER condition:

Employee  Hours
---------------------------------------------
John      1 
Mary      3 
Sue       4

How can something like this be achieved? Can this be done using a single table, or is it necessary to have two tables, one unmerged and the second generated from the first?

Thank you!

[Väinämöinen]

Well I am getting closer and closer... but I am not quite there yet!

I have figured out how to convert the column types back after expanding the subtables. I don't think it is a good feature of PowerBI that it converted those automatically to string, and that I had to figure out how to convert them back manually, but nevertheless what matters is that now I can use some of the expanded columns for further calculations (which was impossible when they were strings) as well as use the expanded date in a date filter.

However, I noticed that there are still TWO huge problems with the grouping/merging itself: in the report, (1) some merging is incomplete so I still get multiple rows per employee, and (2) the aggregations don't work as expected by are multiplied by the number of original rows. In more details:

(1) So for example I might have 10 rows originally with the same employee, and in the QUERY EDITOR I can see they are correctly merged into 1 when I look at them in the query editor after the Grouped Rows step (and if I look at them after the Expanded Full data step, I can see again 10 rows). They also look as still separate rows in the DATA VIEW. However in the REPORT VIEW, they might show up incorrectly as something different e.g. as 2 rows, one corresponding to merging 7 original rows and another corresponding to the remaining 3. 

(2) "Sum"-type and "Counter"-type aggregations in Group By are not really working as expected. For instance, I am including a "Total hours" column that is always supposed to show the sum of all hours done by each employee. Instead, the Total hours column shows the product between all hours and number of rows of that employee that are captured by the date filter. Similarly, as a test column I added a "Row counter" column, but instead of showing the total number of rows per employee, it shows the total number of rows per employee multiplied again per the number of rows captured by the filter!

To clarify, with regard to the sample tables in my first post, these are my EXPECTED RESULTS depending on the Date filter:

Employee  Hours   Total hours   Row counter       (Date filter from 1.1.2018 to 31.12.2018)
---------------------------------------------      -> filter selects ALL original rows
John      3       3             2
Mary      8       8             2
Sue       4       4             1
Bob       6       4             1

Employee  Hours   Total hours   Row counter       (Date filter from 1.1.2018 to 31.01.2018)
---------------------------------------------      -> filter selects TWO original rows
John      2       3             2
Mary      5       8             2

Employee  Hours   Total hours   Row counter       (Date filter from 1.1.2018 to 20.02.2018)
---------------------------------------------      -> filter selects FOUR original rows
John      2       3             2
Mary      5       8             2
Sue       4       4             1

Instead, these are the ACTUAL RESULTS (errors in bold😞

Employee  Hours   Total hours   Row counter       (Date filter from 1.1.2018 to 31.12.2018)
---------------------------------------------      -> filter selects ALL original rows
John      3       6             4
Mary      8       16            4
Sue       4       4             1
Bob       6       4             1

Employee  Hours   Total hours   Row counter       (Date filter from 1.1.2018 to 31.01.2018)
---------------------------------------------      -> filter selects TWO original rows
John      2       3             2
Mary      5       8             2

Employee  Hours   Total hours   Row counter       (Date filter from 1.1.2018 to 20.02.2018)
---------------------------------------------      -> filter selects FOUR original rows
John      2       3             2
Mary      8       16            4
Sue       4       4             1

-------------------------------------------

And here's a summary of my current setup:

QUERY EDITOR

Source data includes ID, Employee, Hours, Date

Grouped Rows features "Group by" = Employee, and three aggregations:  "Total hours" = Sum (Hours), "Full data" = All rows, "Row counter" = Count Rows

Expanded Data selects Date and Hours

Changed Type changes Date back to type->date, and Hours back to type->decimal

REPORT EDITOR

FIELDS selected for my Table visualization are Employee, Hours, Total hours, Full data.Hours, Row counter

Note that Full data.Date is NOT selected to be included in the Table. If I select also Full data.Date, the Table will show all the original rows (with no merging), because each row had different Date values. But because the Date is expanded (and converted), I can use it in a Filter on the report, and that seems to work fine (i.e. when I select a certain date range, only the original rows with matching Date values are included).

[Väinämöinen]

I think I might have solved problem (2) by removing all  aggregations except "Full data" = All rows, and also by setting everything (except Hours) to "Don't summarize". 

On the other hand, I still see some multiple rows that are not supposed to be there. I have at least figured out that what causes this problem is adding a new column to do some calculations.

Basically, in addition to the Hours field I also have fields like Revenue and Cost. I need to show a column that calculates a profit margin, so I defined the column as this:

Profit Margin = ('Table'[Revenue] - 'Table'[Cost]) / 'Table'[Revenue]

When I show the calculated Profit Margin, the rows split up again. I think the reason is, that the Profit Margin is different for each row, because the calculation is actually done row-by row... It took me a while to figure it out, because it most cases the Profit Margin is actually identical when rounded up to the number of displayed digits! So for example I had multiple rows with Profit Margin = 0.75, but the truth is that one row might have been 0.751234 and the next 0.751235.

!!! I think the point of the problem is that I should not calculate the Profit Margin row-by-row, but instead calculate it using the total Revenue and total Cost for all rows that are previously grouped together.

Can anyone help me figure out how to do this? It could be the last missing piece of the puzzle to make this report work.

EDIT: BINGO! 🙂 The solution was NOT to calculate the Profit Margin as a column but rather as a measure: Profit Margin = DIVIDE(SUM('Table'[Revenue]) - SUM('Table'[Cost]), SUM('Table'[Revenue]))

[Väinämöinen]

I am posting an update because eventually I manage to solve all my problems.

I figured out that the remaining problem (1) was because of an additional column in the table that I wanted to have a filter for, say a "Project" column.

Apparently, if I SHOW that column in the report, the table will show one separate row for each different value of this Project, and each one of these rows is the result of aggregating all the original rows. If I instead don't show that column in the table, all original rows are merged into one.

For example, with the original data being this:

ID    Employee  Hours   Date       Project
---------------------------------------------
1     John      2       1.1.2018   Alpha
2     Mary      5       12.1.2018  Alpha
3     Sue       4       4.2.2018   Alpha
4     Mary      3       20.2.2018  Alpha
5     John      1       25.2.2018  Beta
6     Bob       6       3.3.2018   Beta

the report table after merging would look like the following when the Project column is shown:

Employee  Hours   Project
---------------------------------------------
John      2       Alpha
Mary      8       Alpha
Sue       4       Alpha
John      1       Beta
Bob       6       Beta

or it would look like the following when the Project column is hidden:

Employee  Hours   
-------------------
John      3       
Mary      8       
Sue       4       
Bob       6       

It sounds to me like this is the intended behaviour, however it does get a little bit in the way... I think there is a way to still show ONE row per Employee even when there are multiple Project values, and then show something like this:

Employee  Hours   Project
---------------------------------------------
John      3       Alpha, Beta
Mary      8       Alpha
Sue       4       Alpha
Bob       6       Beta

[v-yuta-msft]

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.

PBIX here: https://www.dropbox.com/s/7e5jcxel7yx6ws8/Merge%20rows%20by%20column%20value%20depending%20on%20date...

Regards,

Jimmy Tao

[Väinämöinen]

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!

[v-yuta-msft]

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

[Väinämöinen]

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.

[Väinämöinen]

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?

[Väinämöinen]

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