cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blytonpereira Regular Visitor
Regular Visitor

Bridge table not Cross filtering correctly

I have the following 2 TABLES like below

 

 

snip.JPG

 

 

Now I created separate bridge tables that has UNIQUE values from Table 1 and Table 2 for P2 and for Region.

I connect my bridge table to table 1 and 2 via a one to many relationship SINGLE direction for filtering.

 

On my table visualization on my report page, I am displaying Sales and Forecast on one visualiation, by P2.

 

I then take by Bridge P2 column and also my Bridge Region column to my filters pane.

Now lets say I filter on P2 = 29

On the visualization table, the results appear correctly for only this P2 .

I would also expect my Region Bridge filter to only show ASPAC, however it still shows all regions, in my filters pane.

 

Can someone please suggest a fix for this ?

 

I have attached my workbook here for analysis.

https://drive.google.com/open?id=1A_-Q5i43OcuIHelyqT9rCEHxEb76wxiv

 

The relationships look like this sniee.JPG

 

 

P2_UNIQUE is like an SKU so its a unique identifier. Then other level are higher than that like P4_Unique, Country, Region etc.

 

The bridge tables are used to connect both tables because

1. there are duplicates in each table

2. there may be values existing in one table that are not present in other. So in this way the bridge table will ensure that ALL UNIQUE  values exist from both tables.

3. All the birdge tables is then used in my filters pane.

 

However as mentioned previously this cross filtering is not working properly as it should in the filters pane See below

esc.JPG

 

 

In the example above, I first filter by Albania, then in my Table Visualization i can see the correct list of Brand (Planning Hierarchy 4) that is present in Albania

But then in my filters pane I see the whole list of Unique brand which is incorrect.

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Bridge table not Cross filtering correctly

@blytonpereira,

 

That is because the Cross filter direction is set to Single.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
blytonpereira Regular Visitor
Regular Visitor

Re: Bridge table not Cross filtering correctly

@v-chuncz-msft

Hi thank you for your reply.

This does work in some cases, when the fileds have similar values on both fact tables.

For exmaple in this case there may be Countries on left fact table and have a Forecast value, but they do not exist on the right Fact table.

 

Forecast Tables                       BRIDGE TABLE                             Sales Tables

P2     Country                          P2                                               P2 Country

12    ASPAC                            12                                               12  ASPAC

14    EMEA                              14                                               14 EMEA

16    NORTH                            16                                              

 

Hence,

If i do set one of the Cross filter to BIDIRECTIONAL for example if set P2_Unique (one of my birdge tables which contains a list of ALL my Unique P2 from both Forecast and Sales table) to bidirectional from P2_Unique to my Sales Fact table.....

 

Then I use P2_Unique in my filter pane......Also I use Country_Unique from Sales Fact table.....then when I filter a particular P2, in my filter pane = 16, under Countries I can see nothing, since the P2 16 exists in the other table .......