Report level filter doesn't affect grouped data

[mr_t]

In my query editor I have 2 queries:

• Query 1 is the main query which has all my filtering, column transforms, etc....
• Query 2 is a reference to Query 1, but with an applied "Group By" command on it.

Now in my report I have a report level filter on a column.  When I update that filter it correctly updates Query 1 data.  I would expect the grouped Query 2 data to also be updated since it references Query 1 but that is not the case.  I'm wondering how I can fix this or am I taking the wrong approach.  I want both sets of data to be correctly filtered.

[MFelix]

Hi @mr_t,

Althoigh you are referencing the query 1 on query 2 when you upload the model the the front end of PBI they are two independent tables so if you filter your report by a column of one table the other will not be affected. To make this work you need to create a relationship between both tables by a column i both tables that is common.

Be aware that one needs to have distinct values, if both have repetead items you need to create a dimension table to link both.

However this is not the best approach. If you have 1 query you can make the grouping and slicing without additional tables making the groupings you can use measures or the context ib visuals yo create those groups instead of adding more tables to your model.

My question is why are you duplicating your query to make the grouping ?

Regards
MFelix

[mr_t]

@MFelix  So the relationship solution is close but not quite there.  The new problem I have to fix is this:  

Lets say my data for Query 1 looks like this:
UserID   |   SomeNumber

1            | 1

1            | 2

1            | 3

1            | 4

If I add a filter in the report that says SomeNumber = 3, I would expect Query 2 to only use the rows where SomeNumber = 3 before doing the grouping.  Unfortunately its not respecting the filter.  The relationship solution would only work if my filter was SomeNumber = 5 since that would remove all rows from Query 1 data.

[MFelix]

@mr_t,

As I said believe that the best solution is to make the grouping in DAX or in the front end of power bi using the grouping feature, this way you can use the grouping as context and then make the visual you need  with drill down:

you can also use measure to achieve the grouping.

Can you share a sample of your file and expected result.

Regards,

MFelix

[mr_t]

@MFelix  The reason I am duplicating the data is because I have a visualization that is showing some grouped data.  But the problem is I need to be able "undo" the grouping because I want to show ALL records in the drill down.  My solution was to have 2 visualizations - a pie chart that shows the grouped data and a table that has all the pre-grouped data, which the user can drill down on. 

I'll try to see if I can set up a relationship to both data sets, thanks.