Filter participant counts

Hi Anonymous ,

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

Hi Anonymous,

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.