cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gmckinnon
Frequent Visitor

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

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;relationships.PNG

 

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. 

bar chart line graph.PNG

 

 

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

bar chart line graph time relationship set on qty.PNG

 

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. 

 

bar chart line graph time relationship set on price.PNG

 

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

 

thanks!

1 ACCEPTED SOLUTION
d_gosbell
Super User II
Super User II

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.

View solution in original post

3 REPLIES 3
d_gosbell
Super User II
Super User II

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.

View solution in original post

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

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.

 

relationships.PNG

 

thanks for the help!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors