PBI Direct Lake Multiple fields to one dimension

[gkocev]

I am creating a semmantic model using Direct lake mode. Having the following situation - a fact let's say fact_sales have multiple columns to dim_clients (let's say seller_id and buyer_id). Need to have both client names  shown in the report. Is there any other way besides creating separate physical copies of the dimension (ex. dim_byers and dim_sellers) to achieve this?

Thanks!

[v-kpoloju-msft · Accepted solution]

Hi @gkocev,

Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[gkocev]

Hi, @v-kpoloju-msft, 

Many thanks for your solution. We are testing it at the moment and for the moment it seems to be working super fine. 

We also tested an option by adding multiple shortcuts to a table - it also works as a solution, but someone needs to take care on their creation on Fabric end. 

Another thing we tried is using Tabular Editor for creating table duplicates - works fine until there is a change in the table structure - then refresh of the model is not able to capture the source change for the duplicated tables and there is a need to delete and recreate them. 

So the solution you proposed looks like the best option for this scenario so far. 

One more time - huge thanks! 🙂

If anything else comes up, will post again 🙂

Regards

[gkocev]

HI @v-kpoloju-msft , 

One thing came up - if we want to have slicers to filter by Seller Name and Buyer Name and since in the solution both these columns are measures, do you have an idea how this can be achieved? 

All we could find is usage of some calculated tables which doesn't seem to be an option in Direct Lake mode... 

Thanks!

[v-kpoloju-msft]

Hi @gkocev,

Thank you for following up, and I’m glad the earlier solution was helpful.

You are right since both Seller Name and Buyer Name are represented via measures and you're working in Direct Lake mode, this introduces some limitations, as calculated columns and calculated tables aren't currently supported in this mode.

To enable slicer-based filtering by both roles, a common workaround is to duplicate the dimension table physically (e.g., one for Sellers and one for Buyers), which allows you to create active relationships and use them in slicers. However, I understand that you’re aiming to avoid this.

If you are restricted from creating additional physical tables or relationships, you might explore the option of using composite models with Direct Query over Lakehouse for one of the dimensions, though this introduces some performance and complexity trade-offs.

Here are a couple of useful Microsoft Docs links that explain these constraints and modelling approaches in Direct Lake mode: Direct Lake overview - Microsoft Fabric | Microsoft Learn

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Fabric Community Forum.

[gkocev]

HI @v-kpoloju-msft , 

Many thanks for your response! 

For the moment we decided to proceed with the following approach - for fields where we need to have slicers for filtering, we will create multiple shortcuts, pointing to one table, in a separate WS and will use them as role-playing dimensions (so far this seems to be working fine). For fields where we just need to display values - we will use your approach with the measures. 

Will keep posted when we build the model (as it is quite copmlex one) and we get some real world results if this appoach is feasible 🙂

Thanks!

[v-kpoloju-msft]

Hi @gkocev,

Thank you for the update. Your hybrid approach sounds like a practical and scalable solution, especially for complex models.

Using shortcuts (role-playing dimensions) for slicer-based filtering and leveraging measures for display-only fields is a smart balance between usability and performance. Glad to hear it's working well so far.

Please do keep us posted once the model is built would love to hear how it performs in a real-world scenario. If you run into any roadblocks or need help optimizing the setup, feel free to reach out anytime.

Wishing you success with the implementation.
Thank you.

[v-kpoloju-msft]

Hi @gkocev,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @gkocev,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @gkocev,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[AntoineW]

Hi @gkocev , 

In tabular models (including Direct Lake mode), the best practice is still to create separate role-playing dimensions, i.e., create two separate queries / entities from dim_clients:

• dim_buyers
• dim_sellers

This is because a table can only have one active relationship between two tables at a time in the Power BI / Tabular engine.

Why can’t I just use one dim_clients?

Even though seller_id and buyer_id both point to the same table, the semantic model only allows one active relationship between fact_sales and dim_clients at a time.

You cannot:

• Create two active relationships from fact_sales to dim_clients
• Filter fact_sales by both seller_id and buyer_id simultaneously via a single dim_clients table

Workaround using DAX only (not recommended for complex models):

You could technically:

1. Create one active relationship (e.g., seller_id)
2. Leave the other relationship (buyer_id) inactive
3. Use USERELATIONSHIP() in DAX measures when you want to retrieve buyer name:

[DAX]

Buyer Name =

CALCULATE (

    SELECTEDVALUE ( dim_clients[client_name] ),

    USERELATIONSHIP ( fact_sales[buyer_id], dim_clients[client_id] )

)

⚠️ However, this quickly becomes hard to maintain and cannot be used easily in slicers, visuals, or filters.

Recommended solution: Create role-playing dimensions

Just like for dim_date, the best practice is to duplicate the dimension in the model:

• One table filtered/renamed for buyers → dim_buyers
• One for sellers → dim_sellers

Each one can then have an active relationship with fact_sales, and their attributes (like name) can be used directly in visuals and filters without complex DAX.

• Works well with Direct Lake
• Simplifies report logic
• Supports relationship-based filtering

How to do this in practice (Direct Lake / Fabric):

1. In your Lakehouse or dataflow Gen2, create two queries:
• dim_clients → as dim_sellers
• dim_clients → as dim_buyers
2. You can even filter columns to reduce redundancy
3. In your model: Relate each table with the appropriate foreign key from fact_sales

Hope it can helps !

Best regards,

Antoine