Model 10 program fact tables that all share the same Individuals dimension? Regular patterns break

[YSI]

Hi, there. We’re a non-profit with 10 community programs. Each person (IND_ID) may join any program once per year, so every fact table is IND_ID × Year.

Goal – count unique individuals while letting users slice by
• program-specific fields (e.g., MomsMeet[Location], SummerYouthGroups[Session])
• shared fields (Year, etc.)

See mockup:

Desired slicer behavior
No slicers selected → show the total distinct individuals count across all 10 programs.
Any slicer selected → return only the individuals that satisfy every chosen slicer (intersection).

Important: Also exclude programs that don’t have a slicer applied.

Current model
Dim Individuals (1) → 10 program fact tables (many).

Why the usual “combine facts into one unioned table” approach fails:

Since each program has fields unique to that program. When you slice on that field, it excludes the rows of the other programs. Making those excluded rows not available for further filtering. And the slicers go blank. See image:

Already tried / ruled out

• Bidirectional or Many-to-Many on IND_ID → ambiguous
• TREATAS / CROSSFILTER in each measure → too much maintenance

Looking for: a modelling pattern that keeps other programs and slicers active

Onedrive .pbix is here, I'm glad to check suggestions and report back.

Thanks for any pointers!

---

Moderator note: This is a fully-rewritten, clearer version of my original post (https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-distinct-families-across-multi...); please merge or close the earlier thread if appropriate.

[sjoerdvn]

No, other progams would not be wiped out because the grain is IND_ID/Year, not IND_ID/Year/Program.
What you would need though is a slightly more complex program dimension, that contais a row for every possible or existing combination of programs. 

Somewhat similar to the bridge table, but involving a lot less data.

[YSI]

Thanks, @sjoerdvn after trying with grain IND-Year. Filter context is now retained for other programs but not for other years. See image:

Thanks!

[v-nmadadi-msft]

Hi @YSI  ,
Thanks for reaching out to the Microsoft fabric community forum.

Based on your unique requirement it would be better if you can consider to implement a setup which will take the count of Location independently

from other filters like session

and then combines those values, in this case it is 77.



So to explain the logic of the workaround once again this is to try and bypass rows going blank when slicers are applied indirectly. Since each program has fields unique to that program. When you slice on that field, it excludes the rows of the other programs. Making those excluded rows not available for further filtering. And the slicers go blank.
We are trying to get the count independently for all the three slicers and then summarizing all the three values which will be the count of ID in your case.



If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you

[YSI]

Thanks @v-nmadadi-msft,

Issue we had is that while we know the number of each program, we do not know if they are the same individuals.

What do you think?

[v-nmadadi-msft]

Hi  @YSI ,

The workaround will fall apart for this particular scenario.
As suggested by @techies  bridge table approach may work to get your desired results.

Thank you

[YSI]

@v-nmadadi-msft, thanks for your input and work!

[howellchrisj]

What you may have to do is have another table for the summation and count.  If the slicers overlap between all of the fact tables, great, if not, you can have the summation table have only the filters that are available.  The summation table can be done in Power Query or DAX.
Could you please post your data model?

[YSI]

Hey @howellchrisj thanks for the idea. This is what we tested, is this what you had in mind?

• Built a star style bridge table with one row per IND_ID-Program-Year
• Made a composite key from IND_ID-Program-Year
• Linked that key to each individual program table
• Used a measure for DISTINCTCOUNT(IND-Program-Year['IND_ID']

Like image below: (and a link to the Onedrive .pbix file of that here)

The reason this type of model breaks is because we've been stuck at this modelling wall: all attempts to filter one program, remove all other program rows making them not available for further filtering. As in image:

Issue remained the same with:

• Many-one, one -one and many-many.
• One direction and bidrectional.
• Whether the key was IND_ID-Program or IND_ID-Program-Year

We also considered a table with aggregations but there's too many tables and columns to aggregate.

We want to avoid TREATAS or bidirectional filters unless absolutely necessary.

Really appreciate any pointers you have!