Forum Discussion
Helper IModel 10 program fact tables that all share the same Individuals dimension? Regular patterns break
Helper IHey 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!
Solution SageNo, 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.