Is there a way to create a many to many relationship in the Tabular model. I have one table Data With Fields Project , Amount
I then have a project Device Table With fields Project, Device e.g. A, 1 A,2 A,3 B,1 B,2
I then have a project table With fields Project
And a device table With fields device
The issue is I want to see the data table by device and project.
The main data table contains 32 millions records and the project device table contains 10,000 combinations so a workaround by creating a new fact table by creating a full outer join on the two tables might be a little inefficient , thanks
Best answer by JTreadwell
Hi Stuart, thanks for posting. The core to any many-to-many relation is the bridge table containing foreign keys for both tables that you're trying to relate. I've laid out the setup in the screenshots below:
First, you need to setup your relations properly in the Data Warehouse. As with most relations in TimeXtender, you want to drag from the One side of the relation to the Many side. It just so happens that the Bridge Table is the Many side of both relations.
Next Drag all three tables into the Semantic Model, bringing in the Bridge Table last. This will ensure TimeXtender automatically detects both relationships from the MDW.
Since the fields in the bridge table are only used to relate the model, and not typically used for end-user analysis. You can hide these foreign keys.
Next, you want to ensure that the Filter Direction on both relations under the bridge table in the Semantic model are set to: To Both Tables. This will ensure that when filtering one side of the table, it will filter all the way through the bridge table to the table on the other side.
(To enlarge the below image: Right-click > Open image in new tab)
Once you deploy, execute, and open the model in PowerBI, you would see it displayed like this:
Hi Stuart, thanks for posting. The core to any many-to-many relation is the bridge table containing foreign keys for both tables that you're trying to relate. I've laid out the setup in the screenshots below:
First, you need to setup your relations properly in the Data Warehouse. As with most relations in TimeXtender, you want to drag from the One side of the relation to the Many side. It just so happens that the Bridge Table is the Many side of both relations.
Next Drag all three tables into the Semantic Model, bringing in the Bridge Table last. This will ensure TimeXtender automatically detects both relationships from the MDW.
Since the fields in the bridge table are only used to relate the model, and not typically used for end-user analysis. You can hide these foreign keys.
Next, you want to ensure that the Filter Direction on both relations under the bridge table in the Semantic model are set to: To Both Tables. This will ensure that when filtering one side of the table, it will filter all the way through the bridge table to the table on the other side.
(To enlarge the below image: Right-click > Open image in new tab)
Once you deploy, execute, and open the model in PowerBI, you would see it displayed like this:
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
