Date dimension filtering multiple fact tables

[Anonymous]

I am trying to use a date dimension accross multiple tables in my power BI report, however I can't get the relationships to work.

I am comparing a monthly budget with the mothly accounting. Here is some sample data:

Budget table

Date	Category	Amount
07.2019	R&D	100
07.2019	Administrative	120
08.2019	R&D	150
08.2019	Administrative	75
08.2019	Wages	100
09.2019	R&D	135
09.2019	Administrative	95
09.2019	Wages	100
10.2019	Administrative	20
10.2019	Wages	250
11.2019	Administrative	200

Accounting table

Date	Category	Description	Amount
07.2019	R&D	IT Costs	50
07.2019	R&D	Licenses	60
07.2019	Administrative	Rent	80
07.2019	Administrative	Office supplies	45
08.2019	R&D	IT Costs	100
08.2019	R&D	Licenses	70
08.2019	Administrative	Rent	75
08.2019	Wages	Salaries	95
09.2019	R&D	IT Costs	120
09.2019	R&D	Licenses	40
09.2019	Administrative	Rent	90
09.2019	Wages	Salaries	105
10.2019	Administrative	Rent	20
10.2019	Administrative	Office supplies	10
10.2019	Wages	Salaries	240
11.2019	Administrative	Rent	185

My date table is pretty standard, having all the date attributes, including mm.yyyy I'm using in the previous two tables.

I have a relationship based on the category column between my budget and accounting tables which allows me to compare the amounts, configured as follows:

https://imgur.com/a/uWfxrbE

The end result I'd like to get is this:

https://imgur.com/a/8s1L0rp

A comparison of the budget and the accounting, and a date filter which filters all the data.

I can get an active relationship between my date table and one of the tables, but for the other table I get this message:

"You can't create a direct active relationship betwwen Budget and DimDate because an active set of indirect relationships already exists between these tables. To make this relationship active, set the Cross filter direction to "single", delete, or deactivate any of the indirect relationships first."

How can I get my date table to be linked to all the tables in my report ?

Thanks

[v-jayw-msft]

Hi @valentin,

If I understand you correctly, UseRelationship function might be helpful for you.

Please check following steps as below and see if the result achieve your expectation:

1. Create inactive relationship between Budget and Date:

2. Create measures:

    accounting = CALCULATE(SUM(Accounting[Amount]),FILTER(Accounting,Accounting[Category] = MAX(Accounting[Category])))

    budget = CALCULATE(SUM(Budget[Amount]),FILTER(Budget,Budget[Category]= MAX(Budget[Category])),USERELATIONSHIP(Budget[Date],'Date'[Date]))

3. Result would be shown as below:

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.