Back in September 2024 I wrote a blog post on how to create multiple copies of the same dimension in a Direct Lake semantic model without creating copies of the underlying Delta table. Not long after that I started getting comments that people who tried following my instructions were getting errors, and while some bugs were fixed others remained. After asking around I have a workaround (thank you Kevin Moore) that will avoid all those errors, so while we’re waiting for the remaining fixes here are the details of the workaround.
Let’s say you have a Direct Lake on OneLake semantic model with two tables, a fact table called Conversation and a dimension table called Person. The Conversation fact table has one row for a conversation between two people, but at this point there is only one Person dimension in the model with a relationship from the FromPersonId column on Conversation to the PersonId column on Person:
How can you add a second copy of the Person dimension table without duplicating the data in OneLake?
In Power BI Desktop, while editing the semantic model, go to TMDL View and in the Data pane on the right hand side switch to the Model pane:
Expand Expressions and drag it into the TMDL pane to script it out. It should look something like this:
Then you need to make two changes:
- On the line that starts “expression”, line 3 in the screenshot above, change the name of the expression to something new and unique
- Delete the line that contains the lineage tag, line 8 in the screenshot above
Here’s what it should look like after:
Click Apply and this will create a duplicate Expression in the model. This is the trick that makes everything else work.
Next, create a new script in TMDL View and drag the Person dimension into it to script it out.
Then make the following changes to the script:
- On the line that starts “table”, line 3 in the screenshot above, change the name of the table to something new and unique
- One the line that starts “expressionSource”, line 31 in the screenshot above, change the name of the source expression to that of the new Expression created earlier
- Delete all lines with lineage tags, ie those that start “lineageTag”
- Add one line at the end with the text “changedProperty = Name”
Here’s what it should look like after:
Click Apply and this will create a copy of the dimension in the semantic model.
Then, back in Diagram View, you’ll see the new dimension table but with a warning saying that it hasn’t been refreshed. The next step is to refresh the model using the Schema and Data option:
At this point the new dimension table can be used like any other table, so you can create the relationship between the ToPersonId column on Conversation and the PersonId column on the new ToPerson dimension:
For role-playing dimensions, it would be helpful to also rename the column names (possible in Direct Lake) and the content (not possible without creating another physical table in Direct Lake). E.g. if Date is required to create intersection between (for example) Start Date end End Date, it can be helpful to include a prefix or suffix in the year column so that a report clarifies which role that date has.
Clearly, a report can overcome the limitation by adding labels, but from a semantic model point of view and in terms of general usability, I prefer to import tables that require such “data presentation refactoring” and use Direct Lake for fact tables and large dimensions that don’t need any adjustment.
One issue, at least on my end, is that an error gets thrown when opening the semantic model in Fabric/PowerBI online.
{“code”:”InvalidOperationException”,”message”:”Duplicate sourceId detected: {lakehouse_id}”}
As far as I can tell, everything works fine, it just breaks your ability to look at the model online and requires you to open it in your desktop. Let me know if I am missing something, but that may be a dealbreaker for some.
No, you’re right, it’s another bug. But you are right that Desktop works fine.