Multiple Many to Manys in model (multiple many to manys with a dimension) and related facts

[amylewisAZ]

Hello!

I am working on an analytic model for a non-profit dealing with Grants.  This has some multiple many-to-many scenarios that seem to be frustrating Power BI in the way I set it up. (saying there are no relationships) I tried to use a Kimbal-like approach with a bridge(factless fact)

Business Scenario:

A Grant has a ID, name, and other attributes (type, etc)

A Grant is associated to 1:M Countries  (Area is another Dimension)

A Grant can have 1:M Donors (Donors is another dimension)

2 bridge tables exist:  BridgeGrantArea and BridgeGrantDonor

Fact exists when a grant is awarded.  It is awarded for that GrantDonor Bridge.  Another Fact adds Yearly snapshots for GrantImplementationBudgeting Details (still at GrantDonor level).

DImDonor -> BridgeGrantDonor -> DimGrant -> BridgeGrantArea -> Area

FactGrantAward (GrantKey,DonorKey( (or use the BridgeGrantDonorKey),ApprovedBudgetAmt, etc...)

FactGrantImplementation   (Year,GrantKey,DonorKey( (or use the BridgeGrantDonorKey),ActualImplementationBudgetAmt, etc...)

They are wanting to see things like

Country, # of Donors

Donors # Grants per county

Drill down table with Grant, country, donor......

Any ideas how to make this happen?  I tried messing with the bi-directional filtering and also building another separate fact of Grant Area Donors, ut the additional facts with GrantDonor is frustrating the model more. (and me! :-))

Any advice is greatly appreciated!

-Amy

[AllisonKennedy]

@amylewisAZ  I would try to get 1:many on all relationships where possible. I'm not clear on how area fits in based on your description, but am mapping out something. Does this make any sense with the tables you have? All relationships are 1 to many with 1 on the top, many on the bottom of the two tables.

[amylewisAZ]

Here is an example of themodel I am looking at.  It has the 1:M's using Bridge/Factless facts between the dimensions.  

When I try to build a simple table visual of CountryName, DonorName, GrantName, Power BI errors saying it can't determine the relationship.  I have tried changing the filtering to no avail. 

 

[AllisonKennedy]

@amylewisAZ  Fact GrantArea does not filter Dimension Grant, and therefore DimArea does not filter Fact GrantDonor. You could try setting both direction for FactGrantArea to Dimension Grant, but that will make it a truly many to many relationship, at least in one direction. https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_92.html