Forum Discussion
Use a table multiple time
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 Karmani