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