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
GINMED
Helper III
Helper III

Ambiguity between tables

Hi, there is a problem with my relationships - 

I'm creating visualisation for Actual(GL Entry table) VS Budget(GLBudgetEntry)statements with dimension slicer.

I have stucked in relationships for dimensions - I can not make relationship between Dimension Set Entry table and GL Entry table.

How can I use slicer with dimension (DimensionSetEntry) for both tables in this visualization?

 

Many thanks in advance!

GINMED_0-1661254627564.png

GINMED_2-1661255223255.pngGINMED_3-1661255248758.png

 

5 REPLIES 5
ToddChitt
Super User
Super User

Ah, Dimension tables usually join to fact tables via one-to-many relationships. For any ONE dimension member, there may be many facts. Your data only supports many-to-many. You need to figure out what is going on with the dimension data.

My best trick for this is to create a table visual and add the Dimension Key field to it three times (yes, same field three times). Sealve the first as it is. Set the Aggregation of the others to COUNT and COUNT(Distinct). Finally, sort by the COUNT column and also look at the COUNT and COUNT(Distinct) totals. If they are equal to each other, then you have uniqueness on the column. Otherwise, members that are duplicated in the column will show up with something other than 1 in the COUNT calculation.

You could have 5,000,000 unique values in a table and ONE pair of duplicate values and that will either force the relationship into many-to-many as you have above, or it will fail a data refresh because the incoming data will not support one-to-many.

You need to find and root out those duplicates in your dimension table.

Hope that helps.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





@ToddChitt 

I already made pivoted table for dimensions to have real data.

truptis
Community Champion
Community Champion

Hi @GINMED ,

Since there is a many to many bidirectional relationship it isn't giving you the desired results.

Try to create a bridge table and have relationship that will solve your problem.

 

Please hit the thumbs up and mark it as a solution if this helps you. Thanks.

@truptis thank you, may I ask some example?

truptis
Community Champion
Community Champion

Hi @GINMED,

You can refer this article:

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many 

 

Please hit the thumbs up and mark it as a solution if it helps you. Thanks.

 

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