Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Super User

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
Super User

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.

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors