Excel Data Model not detecting relationships-CodePudding

I am struggling to get this simple data model to work in Excel's Data Model. I have 3 tables. One table is a large budget. The second table is a small budget. The last table is transactions. Each transaction is allocated to both a small budget and a large budget. I think my issue is because we can't use bi direction filtering with Excel's Data Model. When I try to use a field from the transaction table, there is no relationship to any of the budget table. Is there another way to set this up?

Please see pictures attached. Thanks!!!

CodePudding user response：

You're correct that bi-directional filtering is not an option in Power Pivot, and so filtering only ever propagates from the one side to the many side. Which, in your case, means that the Transaction table cannot filter either of the other two tables.

Have you tried adding Calculated Columns to the Transactions table to pull in the Big Budget and Small Budget Values? For example, for Big Budget Value:

=
LOOKUPVALUE(
    BigBudget[Big Budget Value],
    BigBudget[Big Budget], 'Transaction'[Big Budget]
)

CodePudding user response：

I got it I think. You can use the CROSSFILTER measure.

=CALCULATE([Sum of Big Budget Value],CROSSFILTER(BigBudget[Big Budget],'Transaction'[Big Budget],Both))