topic Re: Model 10 program fact tables that all share the same Individuals dimension? Regular patterns break in DAX Commands and Tips

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

YSI — Mon, 04 Aug 2025 06:11:31 GMT

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-multiple-program-fact-tables/m-p/4673648); please merge or close the earlier thread if appropriate.

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

howellchrisj — Fri, 30 May 2025 10:47:55 GMT

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?

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

Anonymous — Fri, 30 May 2025 13:32:29 GMT

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

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

sjoerdvn — Fri, 30 May 2025 15:35:18 GMT

From the information provided I do not see any reason to combine fact tables. Just have your measures combine the data as needed. Have you tried this appraoch and if so, where there any issues you ran into?

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

techies — Fri, 30 May 2025 19:38:30 GMT

Hi YSI are you looking for this, have tried it so far, this works well

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

YSI — Fri, 30 May 2025 19:42:08 GMT

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!

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

YSI — Fri, 30 May 2025 19:43:45 GMT

Thanks Anonymous,

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?

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

YSI — Fri, 30 May 2025 19:45:14 GMT

sjoerdvn, thanks for the idea

Are you suggesting to avoid relationships and instead work the logic with dax alone?

We actually did attempt this and it made each measure to cumbersome to maintain, as there are so many tables and columns.

What do you think?

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

YSI — Fri, 30 May 2025 19:47:28 GMT

Hey techies appreciate the input and work you put in this

Would you mind sharing your model and dax, or the .pbix file? I'd be interested in inspecting!

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

techies — Fri, 30 May 2025 20:52:12 GMT

Hi YSI here is what i come up, not able to add pbix file

create a bridge table using UNION to consolidate multiple fact tables like this

Bridge_ProgramParticipation =

UNION(

SELECTCOLUMNS('Fact MomsMeet',

"IND_ID", 'Fact MomsMeet'[IND_ID],

"Year", 'Fact MomsMeet'[Year],

"Program", "MomsMeet",

"Location", 'Fact MomsMeet'[Location],

"Session", BLANK()

SELECTCOLUMNS('Fact SummerYouthGroups',

"IND_ID", 'Fact SummerYouthGroups'[IND_ID],

"Year", 'Fact SummerYouthGroups'[Year],

"Program", "SummerYouthGroups",

"Location", BLANK(),

"Session", 'Fact SummerYouthGroups'[Session]

SELECTCOLUMNS('Fact MentorMatch',

"IND_ID", 'Fact MentorMatch'[IND_ID],

"Year", 'Fact MentorMatch'[Year],

"Program", "MentorMatch",

"Location", BLANK(),

"Session", BLANK()

SELECTCOLUMNS('Fact SchoolHelp',

"IND_ID", 'Fact SchoolHelp'[IND_ID],

"Year", 'Fact SchoolHelp'[Year],

"Program", "SchoolHelp",

"Location", BLANK(),

"Session", BLANK()

)

create a measure like this

Individuals in All Programs =

VAR SelectedPrograms =

VALUES ( Bridge_ProgramParticipation[Program] )

VAR NumPrograms = COUNTROWS ( SelectedPrograms )

VAR INDsWithProgramCounts =

ADDCOLUMNS (

SUMMARIZE (

Bridge_ProgramParticipation,

Bridge_ProgramParticipation[IND_ID]

"ProgramCount", CALCULATE (

DISTINCTCOUNT ( Bridge_ProgramParticipation[Program] )

)

VAR Result =

COUNTROWS (

FILTER (

INDsWithProgramCounts,

[ProgramCount] = NumPrograms

)

RETURN

IF ( NumPrograms > 0, Result, BLANK() )

Here is how the model is connected

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

YSI — Sun, 01 Jun 2025 22:45:16 GMT

techies thanks! I recreated the Bridge table exactly as you outlined and can confirm that step 1 works: You can choose MomsMeet and SummerYouthGroups and get the numbers in either program.

Where it still falls over is the second step: using slicers unique to each program to further filter this count with an intersection of both slicers.

Scenario:

MomsMeet[Location] = "Toronto"
SummerYouthGroups[Session] = "A"

Expected: 1 Individual (IND_ID 138272 appears in both)

Actual:

Session slicer goes blank (no values left)
Visual shows count = 5 (i.e. only the Location filter applied

I suspect it's because the Location column is BLANK() on the SummerYouthGroup rows and Session is BLANK() on the MomsMeet rows, so each slicer wipes out the other program.

Questions

Would there be a way to solve this using a modelling based approach, so measures stay simple?
If not, is there a viable DAX based approach instead?

Thanks a lot

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

techies — Mon, 02 Jun 2025 11:51:23 GMT

Hi YSI

Yes, you're right about slicers clearing each other out due to BLANKs in the bridge.

pls check this measure that respects each fact table’s native filters and returns the intersection of individuals:

Individuals_Intersection =

VAR MM =

CALCULATETABLE (

VALUES('Fact MomsMeet'[IND_ID]),

ALL('Bridge_ProgramParticipation'),

KEEPFILTERS('Fact MomsMeet')

)

VAR SYG =

CALCULATETABLE (

VALUES('Fact SummerYouthGroups'[IND_ID]),

ALL('Bridge_ProgramParticipation'),

KEEPFILTERS('Fact SummerYouthGroups')

)

VAR Intersection =

INTERSECT(MM, SYG)

RETURN

COUNTROWS(Intersection)

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

johnt75 — Mon, 02 Jun 2025 13:17:30 GMT

One approach that you could explore is to add an Active column to each of your fact tables and append a list of all individuals to each fact table.

In Power Query, add a column to each fact table called Active, set it to true and set the type to true / false, e.g.

#"Added Custom" = Table.AddColumn(#"Changed Type", "Active", each true, type logical),

Create a separate query in Power Query which gets the distinct IDs from your individual table and adds a column called Active, set to false, e.g.

let Source = List.Distinct( Person[Key]), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Key"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Active", each false, type logical), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Key", Int64.Type}}) in #"Changed Type"

You would need to adapt any existing measures to include a filter for 'Fact Table'[Active] = true.

You can now create a measure to count all the matching individuals like

Matching individuals = CALCULATE ( COUNTROWS ( Person ), FILTER ( 'Fact Table 1', ( ISFILTERED ( 'Fact Table 1' ) && 'Fact Table 1'[Active] = TRUE () ) || ( NOT ISFILTERED ( 'Fact Table 1' ) && 'Fact Table 1'[Active] = FALSE () ) ), FILTER ( 'Fact Table 2', ( ISFILTERED ( 'Fact Table 2' ) && 'Fact Table 2'[Active] = TRUE () ) || ( NOT ISFILTERED ( 'Fact Table 2' ) && 'Fact Table 2'[Active] = FALSE () ) ) )

This uses the expanded fact tables to filter the person table. For each fact table, if that table is filtered then it will only return the active rows which meet the existing filter criteria, if the table is not filtered then it will return every individual, and so will act as if that table was not included in the calculations.

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

sjoerdvn — Tue, 03 Jun 2025 12:40:34 GMT

I am certainly not suggesting to avoid relationships, just to use a straight forward model with multiple fact tables and shared dimension. As always with multiple fac tables the relationships must be single direction though.
Yes, there are many fact tables making measures more verbose, but I do not see how the number of columns complicates things.

Since you've uploaded tables & data, maybe add some expected outcomes based on that data.

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

YSI — Wed, 04 Jun 2025 03:34:02 GMT

👍 so it should just be each fact table linked to each dimension? How do you envision the relationships, as apparently I had trouble getting that part. Can I trouble you to outline all the relationships?

Thanks!

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

YSI — Wed, 04 Jun 2025 04:15:34 GMT

techies, very interesting thanks again

Your MM & SYG measure does work for two programs, but we’ll soon have 10 program fact tables. The DAX would grow pretty lengthy and challenging to maintain, and there will be more measures needed in the future.

What I’m hoping for

A relationship pattern that keeps all slicers populated and lets a simple measure to:
- Step 1. Filter by selected Program + Year (shared slicers)
- Step 2. Further filter (by intersection) of each program’s unique slicers (Location, Session, …)
If no clean model exists? Then a single reusable DAX pattern that can the above 2 steps without hard-coding all 10 facts.

Thank you for your help getting this done!

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

sjoerdvn — Wed, 04 Jun 2025 08:59:36 GMT

just make sure to set all relations to many-to-one, single direction, even if it defaults differently when creating.

added measure

# Individuals (complex) = COUNTROWS(DISTINCT(UNION(

VALUES('Fact MentorMatch'[IND_ID])

,VALUES('Fact MomsMeet'[IND_ID])

,VALUES('Fact SchoolHelp'[IND_ID])

,VALUES('Fact SummerYouthGroups'[IND_ID])

)))

also tried simple measure, but results are slighly different (overlap between programs)

# Individuals (simple) = DISTINCTCOUNT('Fact MentorMatch'[IND_ID])

+ DISTINCTCOUNT('Fact MomsMeet'[IND_ID])

+ DISTINCTCOUNT('Fact SchoolHelp'[IND_ID])

+ DISTINCTCOUNT('Fact SummerYouthGroups'[IND_ID])

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

sjoerdvn — Wed, 04 Jun 2025 12:12:50 GMT

I need to make a correction here, as it turns out I misunderstood what you were trying to achieve here; so please ignore my previous comments.

So, rethinking this, my solution would be to build one entirly new fact table, with "grain" or primary key on YEAR and IND_ID, so if an individual joins two programs in any given year there would still be one row representing this in the fact table.
I would preferable build this fact table in the source, but power query would also do the trick.

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

howellchrisj — Wed, 04 Jun 2025 12:27:52 GMT

YSI , I may have missed this if you mentioned, or if someone asked. What is your data source? If it is SQL Server, then the closer you do the manipulations and unions/joins, to the source, the better.

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

YSI — Wed, 04 Jun 2025 16:31:05 GMT

Hey sjoerdvn, thanks for the work. Challenge we have is (as posed in original question)

The requirement was to have users:

Choose with (shared) slicers which programs and years they want to see the count of IND_ID.
Further filter with (unique) slicers in each program's fields to get the INDs that fit only those narrow fields (intersection)

With your approach of making a single table of IND_ID x Year. When slicing by a column unique to just one program, it wipes out the other program rows, making those rows not available for further filtering:

Which model relationship pattern can accomplish this?

The goal is to have a simple measure that allows both shared slicers (Program_name, Year) and unique slicers (Location, Session). Without wiping out filter context of other programs.

That can easily scale to 10 programs and their columns.

Appreciate any direction you may have!