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.

[v-nmadadi-msft]

Hi @YSI ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[YSI]

Thanks @v-nmadadi-msft the issue is still open

So far none the proposals retained all the filter context programs and years, to be available for further filtering.

If anyone has something, I'd be glad to hear.

I'll leave it open in case someone has another idea. Many thanks to all those that gave valuable input so far!

[howellchrisj]

@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.

[YSI]

Hey @howellchrisj 

Enviromnent: SQL Server and can stage as close to that as possible 😀

What still blocks us -a relationship pattern (not dax, that was hard to scale to 10 programs) that can:

Step 1. Filter by shared program slicers such as Program_name to choose which programs yes to see. (OR logic)

Step 2. Further filter using each program’s unique slicers (Location, Session, …) to exclude in each program (intersection of all slicers) which IND_IDs not to see. (AND logic)

The blocker with making a bridge table (your earlier suggestion) was in step 2: that when the user chooses a program unique slicer like Location, it wipes out the other program rows, making them not available for further filtering. Putting an image, this remains the issue still.

If you have any input, that may solve this fundamental issue.

Thanks again for your help!

[howellchrisj]

This looks accurate per your example of what data should show.  

I would be interested to see the full data model, I suspect you need more dimension tables and in the visuals, you can select the option to show records with no data potentially.   But this sometimes defeats the purpose of having the dimensions.

[YSI]

Hi @howellchrisj,

Happy to share! The example actually comes from a synthetic PBIX (no real life data, columns or slicer values) due to privacy concerns. In any case, the file using your bridge table idea is here, and original file here

If you need to inspect anything else, let me know and I’ll be glad to take a 60-second screen recording displaying why the rows and slicers blank out.

Thanks again for looking at this.

[johnt75]

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.

[techies]

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

 

[YSI]

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!

[techies]

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

[YSI]

@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

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

Thanks a lot

[techies]

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)

[YSI]

@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

1. 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, …)

2. 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!

[sjoerdvn]

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?

[YSI]

@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?

[sjoerdvn]

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.

  

[YSI]

👍 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!

[sjoerdvn]

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])

[sjoerdvn]

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.

[YSI]

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

The requirement was to have users:

1. Choose with (shared) slicers which programs and years they want to see the count of IND_ID.
2. 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!