User Profile
amylewisAZ
Frequent Visitor
Joined 6 years ago
User Widgets
Contributions
DAX help needed when dealing with multiple bridge tables (many to manys) and multiple dates
Hi All, I am struggling to get the correct DAX when Data Model: Formulas: -- Awarded Grants Counts Total Grants Awarded = CALCULATE(DISTINCTCOUNT('Grant Award Info'[GrantID]) ,USERELATIONSHIP('Date'[Date], 'Grant Award Info'[AwardDate]) ) TotalDonorBudgetApproved = CALCULATE(SUM('Grant Award Info'[DonorLOABudget]),USERELATIONSHIP('Date'[Date], 'Grant Award Info'[AwardDate])) -- Not Awarded: Total Grants NotAwarded = CALCULATE( DISTINCTCOUNT('Grant Pipeline History'[GrantKey]), FILTER (ALL('Grant Pipeline History'[Grant Status]), 'Grant Pipeline History'[Grant Status]= "Not Awarded"),USERELATIONSHIP('Date'[Date],'Grant Pipeline History'[Status Date])) Total Not Awarded $ = CALCULATE( SUM('Grant Submission Info'[TotalDonorBudgetSubmitted]), FILTER (ALL('Grant Pipeline History'[Grant Status]), 'Grant Pipeline History'[Grant Status]= "Not Awarded"),USERELATIONSHIP('Date'[Date],'Grant Pipeline History'[Status Date])) Issue: here you can see the issue... top one is the totals with only the GratDetail.Domain (no many to many relationships.... 2nd one is with Region and the totals... you can see the grand totals are correct, but the details are not. last one is area and same issue. Any Advice? Thank you in advance, AmyRe: Multiple Many to Manys in model (multiple many to manys with a dimension) and related facts
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.Multiple Many to Manys in model (multiple many to manys with a dimension) and related facts
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
