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

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's avatar
sjoerdvn
Icon for Solution Sage rankSolution Sage
1 year ago

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

    👍 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's avatar
      sjoerdvn
      Icon for Solution Sage rankSolution Sage
      1 year ago

      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's avatar
        sjoerdvn
        Icon for Solution Sage rankSolution Sage
        1 year ago

        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.