Date and Time dimension cant set both as active on two fact tables - ambiguity error

[gmckinnon]

Hi I have what I would think a common issue but can't seem to find any example of this being doen before.

I have two fact tables both have a datetime stamp and I extracted the date into one column and the time into another for both tables.

I then created a date dimension and linked both fact tables to it via the date columns which works as expected. I then created a time dimension but power bi only allows me to set the relationship active for one of the fact tables to time dim.

See below;

The problem I have is I am trying to create a visual where Both facts are representated, one in the bar chart and one on the line values and I want the user to have the ability to drill down from year down to time but this only works for one of the facts.

Below shows At the "date" level which is fine. 

And if I drill down you can see how it looks;

Because the relationship is set active on the "generation" fact that works as expected but the price does not because the relationship is stuck on inactive.

if I swap the relationships around it does the oppisite. 

Any idea? The only thing I could think of is combining the date and time dimensions into one. 

thanks!

[d_gosbell · Accepted solution]

The problem here is that having bi-directional filters on all 4 of the relationships in your picture creates an ambiguous loop of relationships. If you change all the relationships to single directional you should be able to activate that 4th relationship and all your charts should still work fine.

[gmckinnon]

thanks d_gosbell, that was it, In the past I've found I needed to set relationships to both directions for whatever reason so I was in the habit of always doing that, probably was my misunderstanding. 

I'l go look into what the difference is between the diferent directions

[gmckinnon]

Hi,

I have two fact tables, a price and genreation as seen below both connected to date and time dimensions.

I need to create a measure volume weighted average price which will be like sumx(generation*rrp)/sum(generation)

but I can't wrap my head around how to do this, I thought I could use the "use related" dax but these are not directly connected together. Should I create another table joining these together by date and time? 

I want to be able to drill down on the visual so it calcs volume weighted price from year down to time levels.

thanks for the help!