Forum Discussion

Väinämöinen's avatar
Väinämöinen
Frequent Visitor
8 years ago

Merge rows by column value depending on date filter

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 ------------------------------...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Community Support
8 years ago

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%20filter.pbix?dl=0

 

Regards,

Jimmy Tao

Väinämöinen's avatar
Väinämöinen
Frequent Visitor
8 years ago

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