Forum Discussion

YSI's avatar
YSI
Icon for Helper I rankHelper I
1 year ago

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

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...
Data Modeling
DAX
Need Help
howellchrisj's avatar
howellchrisj
Icon for Helper I rankHelper I
1 year ago

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's avatar
    YSI
    Icon for Helper I rankHelper I
    1 year ago

    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!