Bridge table not Cross filtering correctly

[blytonpereira]

I have the following 2 TABLES like below

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

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

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.

[v-chuncz-msft]

@blytonpereira,

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

[blytonpereira]

@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 .......

[Anonymous]

Try to make ONE bridge table with P2 and Country value:

f.eks P2-Country as Fkey in all the three tables and connect them with eachother.

Your can add seperate columns for P2 and Country in the bridgetable to filter both factstable either by P2 or Country

Bridgetable
FKey                P2           Country
12-ASPAC