Filter participant counts

[displaced]

Hi,

I've hit a stubling block which I cant quite figure out.

I'm using power query to manipulate various sources of data.  several tables each covering a year of particiapnt data from 3 distinct sources.  I roll these into one sheet covering all years per source and the roll that into one overall sheet called Master Participant.  This now includes all participants from all years from all sources.  I've merged into this table, location info based on the particiapnt address

I wasnt interested in the detail of these sheet as I only wanted a count per location.  So I've removed all other columns and grouped by location to get a count of rows per location.  Giving me a count of particiapnts per location. Great!!

What has now been asked for is to be able to filter within the powerbi dashboard based on the 3 original distinct sources.  But when i added a filter with the source column from the orignal table, it doesnt filter my master participant table as there is no relationship in play.  The source column is no longer available in the Master participant table.  I cant work out how to create a relationship.

What am i missing here ??? 

[BA_Pete · Accepted solution]

Hi @displaced ,

I think there's a couple of options here:

-1- Retain the source column in your Master Participant table by doing the final Group By on [Source] and [Location].

-2- Don't do the final Group By at all and just send the granular appended table to the model and calculate whatever you need using measures over the full table. This one also future-proofs against the new requirements you'll get tomorrow, and the next day, etc.

Pete

[BA_Pete · Accepted solution]

Hi @displaced ,

I think there's a couple of options here:

-1- Retain the source column in your Master Participant table by doing the final Group By on [Source] and [Location].

-2- Don't do the final Group By at all and just send the granular appended table to the model and calculate whatever you need using measures over the full table. This one also future-proofs against the new requirements you'll get tomorrow, and the next day, etc.

Pete

[cengizhanarslan]

1) Keep the detail table

Your Master Participant table should stay at participant grain, e.g.:

ParticipantID | Location | Source | Year | …

2) Create the count using a DAX measure

Instead of grouping in Power Query, create a measure:

Participant Count =
COUNTROWS ( 'Master Participant' )

Put:

• Location on rows (table / map / matrix)

• Participant Count as the value

• Source as a slicer

Now filtering works automatically, because:

• Source still exists

• Filter context flows into the measure

• The count is recalculated dynamically

[v-sgandrathi · Accepted solution]

Hi @displaced,

Thank you @cengizhanarslan @BA_Pete for your prompt replies to the  query.

Another concept that explains why this occurs is that grouping in Power Query changes the grain of your data. After aggregating the table to just Location, attributes at a more detailed level, such as Source, are no longer available, which prevents Power BI from creating relationships or passing filter context.

If you need to keep the pre-aggregated table for performance or model size reasons, there are two advanced options:

• Create a bridge table with [Location, Source] grain and link both the grouped table and a Source dimension to it. This keeps filter flow intact without duplicating participant-level data.
• Use a disconnected slicer and TREATAS in a measure to apply the selected Source to the aggregated table. This is useful when you can't remodel, but it's more complex and harder to maintain.

While these solutions are available, they are workarounds. The best approach is to keep data at participant grain and use DAX for aggregation, making future slicing easier without needing to redesign the model.

Thank  you.

[displaced]

Thank all,

@v-sgandrathi @cengizhanarslan @BA_Pete 

Really appreciate your very detailed and thought through explanations, it has made it clear how I have created this problem and steps I can take to fix it.  

I think in my example, reworking the model is the best way forward rather than any work arounds.  So keeping the grain of the source and the working with DAX later to get the totals.

Much appreciated. 

[v-sgandrathi]

Hi @displaced,

The issue arises because grouping in Power Query changes the data grain to just Location, which removes details like Source and blocks filter context in the model. Keeping the Master Participant table at the participant level and not pre-aggregating preserves all columns such as Source, Year, and Location. This allows Power BI relationships and slicers to function as intended. You can then use a DAX measure like COUNTROWS('Master Participant') to calculate participant counts dynamically. This method follows best-practice modeling, keeps your model flexible, and ensures filters like Source work automatically without needing to redesign the data model.

Thank you.

[v-sgandrathi]

Hi @displaced,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?

If you still have any questions or need more support, please feel free to let us know. 

We are more than happy to continue to help you.