Forum Discussion
Helper IModel 10 program fact tables that all share the same Individuals dimension? Regular patterns break
Helper I👍 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!
Solution Sagejust make sure to set all relations to many-to-one, single direction, even if it defaults differently when creating.
added measure
also tried simple measure, but results are slighly different (overlap between programs)
- 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:
- 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!
- sjoerdvn
No, other progams would not be wiped out because the grain is IND_ID/Year, not IND_ID/Year/Program.
What you would need though is a slightly more complex program dimension, that contais a row for every possible or existing combination of programs.Somewhat similar to the bridge table, but involving a lot less data.