Ambiguity between tables

[GINMED]

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!

[ToddChitt]

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.

[GINMED]

@ToddChitt 

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

[truptis]

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.

[GINMED]

@truptis thank you, may I ask some example?

[truptis]

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.