Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more
Hello, let me try explaining the issue with a simple example.
I have a fact table with two country-keys: one for sender, one for receiver.
The sender-country-key and receiver-country-key are both joined to the country table in my Power BI model.
Country : Fact = 1 : N.
So , I have an active and inactive relation.
I can create two measures using USERELATIONSHIP for the inactive:
So far, so good.
Sender and receiver in the fact table are joined by surrogate key to the country table.
For the end-user I hide the keys, so in the fact table (folder) the end-user doesn’t see them and the country table (folder) only shows the logical country name like ‘Italy’.
If the end-user wants to see Sender Sales by Country, he picks the logical country from the Country table (folder) and the measure 'Total Sales by Sender Country'. If for instance he wants to add Transaction Type, which is part of the fact table, to the visual, this will work. Etcetera.
But here it comes.
I did hide the country keys in country and fact table, because these are meaningless keys for the end-user.
But suppose the end-user builds his own report and only wants to see the facts where the sender-country is ‘Scotland’.
I can drag the logical country name from the country table to the visual. But how do I know whether this is a sender-country or receiver-country??
Does this mean that, in case I have a role playing dimension and I hide the surrogate, meaningless keys, I have to add the logical keys of it to the fact table? So, I have to add sender-country-name and receiver-country-name to the fact table?
Hope someone can help
regards
Ron
@MFelix @lbendlin
You are correct.
You have one country dimension and two measures. If you want to show both sender and receiver country at the same time then indeed you need to pull these into the fact table.
Thnx @lbendlin
But also If I don't want to show them both at the same time, I need to pull there logical names into the fact table. Otherwise how does the end-user know he is dealing with a sender- or receiver-country, since the country table only shows one general country name. Am I right?
And same would be if I have for instance an order-date and ship-date, both part of a role playing dimension. If the end-user wants details of all the items that were ordered on '2022-05-06' there should be a field 'order-date' in my fact table.
Remember you created a second measure with USERELATIONSHIP. Any date list will assume the role assigned to it by the measures.
I don't understand exactly what you mean, but suppose the end-user wants all the rows where order-date = '2022-05-05', he is not using the measure but just getting row information from the fact table.
Or do you mean that when oder-date is not the active relation (only one relation can be active) you can't get additional information from your date table?
If so, the end-user should be fully aware of that ....
The date is interpreted by the Vertipaq engine according to the relationship in the measure. If you want to show items ordered in a certain month you use the order measure. If you want to show items shipped in the same month you use the ship measure.
What you cannot do in this scenario is show the orders with their order date and ship date (since you chose to hide these fields)
You are right. I hide them because they are surrogate keys, no use for end-user. But if it want to see order and ship date I can, in my view which is input for the model, translate the surrogate keys to logical keys.
But suppose ship date is the inactive relationship I won't be able to get information out of the date table, other then when using the measure. Am I right?
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!
Check out the November 2025 Power BI update to learn about new features.
| User | Count |
|---|---|
| 59 | |
| 43 | |
| 42 | |
| 23 | |
| 17 |
| User | Count |
|---|---|
| 190 | |
| 122 | |
| 96 | |
| 66 | |
| 46 |