Count distinct families across multiple program fact tables—unique columns break the usual patterns

[YSI]

Hi, first-time poster 👋

Goal
How can I count distinct ACT_ID (families) across multiple program fact tables, while letting users slice by shared dimensions (Year, State) and program-specific attributes (e.g. MomsMeet[Location])

At the core we're counting how many distinct ACT_IDs there are, filtered by the columns in the various programs.

Background: We’re a non-profit with many programs; each program stores the participants in its own fact table. New columns are added to each program and new programs are added, limits common workarounds (like aggregations).

Goal: A model that'll enable counting how many distinct families participate across all of our programs. With slicers to filter only families in some programs, or only those those that equal certain values in columns unique to that program (like MomsMeet[Location]). See image.

Simplified model
Dimensions
• Dim Accounts (ACT_ID, State) this is the family table
• Dim Individuals (IND_ID, ACT_ID, Name, Gender)

• Dim Programs (Program_ID)
• Dim Years (Year)

Facts (few examples)
• Fact MomsMeet (IND_ID, Year, Program_ID, Location, …)
• Fact SummerYouthGroups (IND_ID, Year, Program_ID, Session...)
(each fact is unique on IND_ID + Year; largest ≈ 60 k rows, this may help a lot)

What I’m after
• Modelling pattern that preserves program-specific slicers.
• Avoid heavy DAX (like  USERELATIONSHIP , CROSSFILTER, TREATAS per slicer) if possible

Onedrive .pbix file with synthetic data and program names. Or see model diagram below. Thanks for any pointers! 😀

I'll write in a comment which approaches I tried but didn't work.

[v-csrikanth]

Hi @YSI 
Could you please check the above response of mine attached the pbix file.
Let us know what is the issue you are facing describe us in detail.
If possible do attach the screenshot with the requirements and pbix file.

Best Regards,
Cheri Srikanth

[YSI]

Hi, none of the responses have resolved the issue as of yet. Thanks for following up, and I will keep it open.

[v-csrikanth]

Hi @YSI 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @YSI 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @YSI 

Thanks for reaching out to fabric community.
Sorry for the late response.

You can try the below approach that will works your requirement.

1. Create a new table-
   CombinedIDS = UNION(Fact_Momsmeet,Fact_SummerYouth)
2. Modelling changes-
   Connect CombinedIDs with DimYears (Year)
   Connect CombinedIDs with Fact_SummerYouth(IND_ID)
3. Create the DAX-
   
   TotalDistinctFamilies2 =

VAR MomsMeetIDs =

    CALCULATETABLE (

        VALUES ( Fact_MomsMeet[ACT_ID] ),

        ALLSELECTED ( Fact_MomsMeet )

    )

VAR SummerYouthIDs =

    CALCULATETABLE (

        VALUES ( Bridge_ProgramParticipation[ACT_ID] ),

        ALLSELECTED (Fact_SummerYouth )

    )

-- Added more programs here...

VAR CombinedIDs =

    UNION ( MomsMeetIDs, SummerYouthIDs )

RETURN

    DISTINCTCOUNT ( CombinedIDs[ACT_ID] )

You will get below result-

Below is the attahced pbix file for reference-

If the above information helps you, please give us a Kudos and marked the Accept as a solution.

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @YSI 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[YSI]

Thanks @v-csrikanth! Doesn't work in my case because choosing a slicer is completely removing all other rows, making it not possible to further filter out based on fields on the removed rows.

See the example in your power BI file.

In the visuals pane, choosing New Brunswick should allow for further filtering of session, but it's blank and not showing July/August.

[v-csrikanth]

Hi @YSI 
Thank you for being part of the Microsoft Fabric Community.
As requested please do follow the steps to resolve your issue and check the attached pbix file and build your custom visuals.

1. Prepare Data in Excel
Use the data in your uploaded screenshot to create three sheets in Excel (or separate CSVs if you prefer):

Sheet 1: Fact_MomsMeet

*************************************************************************************

ACT_ID IND_ID Year Program_ID Location
100 149141 2024 2 East Brunswick
200 136313 2022 2 East Brunswick
100 115799 2023 2 Atlanta
200 173370 2024 2 Atlanta
100 149141 2023 2 Atlanta
*************************************************************************************

Sheet 2: Fact_SummerYouth

ACT_ID IND_ID Year Program_ID Session
200 136313 2023 1 July
100 115799 2023 1 July
200 173370 2023 1 August
*************************************************************************************

Sheet 3: Dim_Accounts

ACT_ID State
100 Texas
200 Georgia
*************************************************************************************

Sheet 4: Dim_Years

Year
2022
2023
2024
*************************************************************************************

2. Load Data in Power BI
Import these four sheets as separate tables.

3. Create Bridge_ProgramParticipation Using DAX
Create a new table (Modeling → New Table):

dax
Copy
Edit
Bridge_ProgramParticipation =
UNION(
SELECTCOLUMNS(
Fact_MomsMeet,
"ACT_ID", Fact_MomsMeet[ACT_ID],
"Year", Fact_MomsMeet[Year],
"Program", "MomsMeet",
"Location", Fact_MomsMeet[Location],
"Session", BLANK()
),
SELECTCOLUMNS(
Fact_SummerYouth,
"ACT_ID", Fact_SummerYouth[ACT_ID],
"Year", Fact_SummerYouth[Year],
"Program", "SummerYouthGroups",
"Location", BLANK(),
"Session", Fact_SummerYouth[Session]
)
)

4. Create Relationships
Bridge_ProgramParticipation[ACT_ID] → Dim_Accounts[ACT_ID]

Bridge_ProgramParticipation[Year] → Dim_Years[Year]

5. Create Measure
TotalFamilies :=
DISTINCTCOUNT(Bridge_ProgramParticipation[ACT_ID])

6. Add Slicers & Charts
Use visuals based on:

TotalFamilies (card)

Bridge_ProgramParticipation[Program] (slicer)

Bridge_ProgramParticipation[Location] (slicer)

Bridge_ProgramParticipation[Session] (slicer)

Dim_Years[Year] (slicer)

Now you'll see the total family count adjusting dynamically across all program-specific and shared filters.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[YSI]

Interesting! Has the same problem as approach 3 I mentioned in earlier comment: 

• Slicing on a program-specific column (like MomsMeet[Location] grays out the other rows, making it not possible to filter out values in those rows

Let's say you have image one.

Then you filter MomMeet[Location]. Now the rows of SummerYouthGroups get filtered out, and not able to be further filtered by Session July or August. See image 2, where it did this visually.

Also a measure like this needs lots of TREATAS or other DAX to make relationships. Hard to maintain.

[v-csrikanth]

Hi @YSI 
Thank you for being part of the Microsoft Fabric Community.
You can utilize DAX functions to combine the FamilyID columns from all fact tables and then count the distinct values.

TotalUniqueFamilies =
COUNTROWS(
DISTINCT(
UNION(
SELECTCOLUMNS(FactTable1, "FamilyID", FactTable1[FamilyID]),
SELECTCOLUMNS(FactTable2, "FamilyID", FactTable2[FamilyID]),
SELECTCOLUMNS(FactTable3, "FamilyID", FactTable3[FamilyID])
-- Add additional SELECTCOLUMNS for more fact tables as needed
)
)
)

This method effectively aggregates unique FamilyID values across all specified fact tables.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[YSI]

Approaches I tried

1. Single appended fact table of all programs

• Slicing on a program-specific column (like MomsMeet[Location] grays out the other rows, making it not possible to filter out values in those rows

2. Wide flat table

• Row per IND_ID: Tens of mostly empty columns - unmanagable
• Row per IND + Year: When a program specific slicer takes out a row, it only removes that IND + Year, but keeps that IND in other Years. It should remove the IND completely.

3. Classic star-schema variants

• Combined program table linking to fact program tables: Same graying out row issue as #1
• Dim IND hub with bidrectional links to fact tables: counts are ok, but you can't add shared dimension tables without becoming ambiguous
• Dim IND hub with Many to Many links to fact tables. Blocks filter context for non-key columns—and we’d rather steer clear of M:M relationships.

4. Dax workarounds (USERELATIONSHIP, CROSSFILTER, TREATAS)

• What I found was too complex to maintain. Some approaches needed formulas for every slicer.