Bridge Table Not Working

[dokat]

Hi,

I have two data tables POS and Region with M:M relationship. I created bridge table so measures by both tables can be controlled by one slicer. However after creating bridge table and slicer i can only control POS table and not Region. Not sure what's causing this problem but appreciate any help

[jdbuchanan71 · Accepted solution]

Thank you for sharing that.  I made some measures to just SUM the Value column from POS and Region tables so I could look at the amounts.

_POS_Value = SUM ( 'POS'[Values] )

_Region_Value = SUM ( 'Region'[Values] )

I also made a measure to look at the difference between your two test measures.

Test Measure Variance = [Region Test Measure] - [POS Test Measure] 

Looking at a breakdown of the number by brand under the correction category the Bridge[Brand] 'Liquid Paper 2-in-1 Bottle' only has a total amount in the 'POS'[Values] column of 81k where the Region'[Values] column has 498k so that looks like most of your variance.

Most of the other amounts are very close.

Could it be a problem with data missing from the POS table?

[dokat]

@jdbuchanan71 Thank you for pointing me to right direction.

[jdbuchanan71]

Can you share your .pbix file? (post it on drop box and share the link)

[dokat]

@jdbuchanan71 Yes, please see below link. Region and POS Test Measures should return same dollars in both tables. Please let me know if you have any questions.

https://www.dropbox.com/s/hzmrram3a38z3s3/Bridge%20Table%20Sample%20File.pbix?dl=0

[truptis]

Hi @dokat 

Try making it a bidirectional relationship. It might solve the problem.

[dokat]

@truptis Thanks for your reply. I tried it please see below however it still doesnt filter correctly. It bring All Other Brand whether they belong to filtered category in slicer or not.

[truptis]

@dokat - can you change the cross filter direction to both? It might resolve your problem.

[dokat]

@truptis How can i do that?

[jdbuchanan71]

If you right click on the line and go to Properties you can make it active.

[dokat]

@jdbuchanan71 It gave me different option, i didnt see properties i clicked on manage relationships and put a check mark near region table. Now slicer selection returns values from Region table however they are not correct. Please see below.

[jdbuchanan71]

Odd, in your manange relationships screen shot the relationship is active but in your first one it is not (the dotted line).

[dokat]

@jdbuchanan71 Do you i need to create new relationship between Region and Bridge for each slicer?

For Ex: If i want to have Category slicer do i need to create a new relationship between two tables? Ithought because Bridge table links POS and Region table via Brand i didn't have to to create new relationship. Currently i am getting all incorrect measure results.

[dokat]

@jdbuchanan71 It was deselected, after i put a checkmark i think thats when it became active.

[jdbuchanan71]

@dokat 

The relationship between Bridge and Region is inactive.

[dokat]

@jdbuchanan71 Thanks for your reply. How can you activate it?