Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
That is because the Cross filter direction is set to Single.
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 .......
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |