Skip to main content

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

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: 

  1. 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.
  2. 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. 
  3. 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. 
  4. 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: 

 

Please let me know if you have any questions. 

Reply


Terms and ConditionsCookie settings