Forum Discussion
Use a table multiple time
Hi
I have a referential table with code and countries and nationalities
I have a table with my users and a code for the country of the adress and a code for the nationality.
How can i use my referential countries/nationalites table multiple time to join to my user table to obtient the country and nationaliy label.
Is it possible to duplicate a table in the sementic model ?
thx
Hi ShaunLeMouton,
Hope you're doing well!
So yes, duplicating a table in the semantic model is absolutely possible via a DAX calculated table, and it's the standard pattern for this exact use case.
the clean solution is to create multiple inactive relationships between your user table and your referential table, then use USERELATIONSHIP in your DAX measures.
But if you want the labels directly as columns (which is usually the case here), the better approach is to use calculated columns with RELATED + USERELATIONSHIP, except that combo doesn't work directly. So the real pattern is duplicate the referential table using a calculated table.
In your semantic model, create a second table that's just a copy of your referential:
Nationalities = Countries
This creates a second logical table pointing to the same data. Then:
- Create a relationship between Users[address_country_code] → Countries[code]
- Create a second relationship between Users[nationality_code] → Nationalities[code]
Both relationships are active, each pointing to a different logical table, so there's no ambiguity. You then just use RELATED(Countries[label]) and RELATED(Nationalities[label]) directly as calculated columns, no tricks needed.
4 Replies
Hi ShaunLeMouton,
Hope you're doing well!
So yes, duplicating a table in the semantic model is absolutely possible via a DAX calculated table, and it's the standard pattern for this exact use case.
the clean solution is to create multiple inactive relationships between your user table and your referential table, then use USERELATIONSHIP in your DAX measures.
But if you want the labels directly as columns (which is usually the case here), the better approach is to use calculated columns with RELATED + USERELATIONSHIP, except that combo doesn't work directly. So the real pattern is duplicate the referential table using a calculated table.
In your semantic model, create a second table that's just a copy of your referential:
Nationalities = Countries
This creates a second logical table pointing to the same data. Then:
- Create a relationship between Users[address_country_code] → Countries[code]
- Create a second relationship between Users[nationality_code] → Nationalities[code]
Both relationships are active, each pointing to a different logical table, so there's no ambiguity. You then just use RELATED(Countries[label]) and RELATED(Nationalities[label]) directly as calculated columns, no tricks needed.
For this scenario the cleanest approach is to have two copies of your countries/nationalities table in the model, one acting as a Country dimension and one as a Nationality dimension. Each copy connects to your users table on its own relationship (Users[CountryCode] to CountryDim[Code], Users[NationalityCode] to NationalityDim[Code]).
The easiest way to create the second copy is in Power Query. Right click your existing referential query and choose Reference, then rename it (for example NationalityDim). Both tables stay in sync with the source that way.
The alternative is to keep a single referential table and create both relationships in the model, marking one as inactive. Then in any measure where you need the inactive side you wrap it with USERELATIONSHIP, for example:
Nationality Users = CALCULATE([Users Count], USERELATIONSHIP(Users[NationalityCode], Countries[Code]))
I usually prefer the duplicated dimension approach because it is simpler to use in slicers and visuals. Role playing with USERELATIONSHIP only works inside measures.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best regards,
Shai KarmaniHello ShaunLeMouton yes, in your scenario you can use a Duplicate or a Reference table from the same lookup query.
The Difference between Duplicate and Reference in Power Query | Towards Data Science
Hi
thx for your answers. i use FABRIC , and Direct Lake with my Gold data Lake with my tables.
if i understand i must create in my gold lake a specific table for each case (Nat 1,Nat 2, country1 , country2...) , I need a relation beetween my referential table and my table user ?
