topic Re: Multiple Many to Manys in model (multiple many to manys with a dimension) and related facts in DAX Commands and Tips

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

amylewisAZ — Thu, 17 Dec 2020 21:27:21 GMT

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

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

AllisonKennedy — Mon, 21 Dec 2020 09:00:23 GMT

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.

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

amylewisAZ — Mon, 21 Dec 2020 18:56:04 GMT

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.

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

AllisonKennedy — Mon, 21 Dec 2020 20:48:23 GMT

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