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

Hey howellchrisj 

 

Enviromnent: SQL Server and can stage as close to that as possible 😀

What still blocks us -a relationship pattern (not dax, that was hard to scale to 10 programs) that can:

 

Step 1. Filter by shared program slicers such as Program_name to choose which programs yes to see. (OR logic)

 

Step 2. Further filter using each program’s unique slicers (Location, Session, …) to exclude in each program (intersection of all slicers) which IND_IDs not to see. (AND logic)

 

The blocker with making a bridge table (your earlier suggestion) was in step 2: that when the user chooses a program unique slicer like Location, it wipes out the other program rows, making them not available for further filtering. Putting an image, this remains the issue still.

 

 

If you have any input, that may solve this fundamental issue.

 

Thanks again for your help!

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

This looks accurate per your example of what data should show.  

I would be interested to see the full data model, I suspect you need more dimension tables and in the visuals, you can select the option to show records with no data potentially.   But this sometimes defeats the purpose of having the dimensions.

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

    Hi howellchrisj,

     

    Happy to share! The example actually comes from a synthetic PBIX (no real life data, columns or slicer values) due to privacy concerns. In any case, the file using your bridge table idea is here, and original file here


    If you need to inspect anything else, let me know and I’ll be glad to take a 60-second screen recording displaying why the rows and slicers blank out.

     

     

     

    Thanks again for looking at this.