Split column into lines one-to-many relationship best practice

[Oimat]

Hello, 

It's written everywhere in Power Bi PL-300 exam that a good schema is the star one, with Dimension tables related to fact table as one-to-many relationship. 

Fact table : Employee days

Dimension table : projects & project codes

The relationship between the two tables is with project codes.  But in  my Projects sharepoint list, the column Project codes is like "Code1;Code2;Code3" and the Code Owner is "SuperMan, IronMan" , meaning that after the split, I get multiple lines

Dimension table

Project1 ; Code1; Superman

Project1 ; Code2; Superman

Project2 ; Code3; Superman

Project2 ; Code3; Ironman

Project3 ; Code4; Antman

Project4 ; Code5; Superman

Project4 ; Code5; Ironman

Project4 ; Code6; Superman

Project4 ; Code6; Ironman

Fact table 

Employee1; 11 days; Code1

Your see my dimension table can not have anymore a one-to-many relationship with the fact table, since Code is duplicated. All my tables have this "problem". 

What would be the expert way of handling this ? Is it really a problem ?

Thank you

[MasonMA · Accepted solution]

Hi @Oimat 

Please consider decomposing the many-to-many relationship using bridge tables. You can create different Dimension tables with Distinct columns like below.

From your Dim table

Duplicate the DIM query into a query with Code and create one DimCode table with unique values. 

When modelling your relationship create your DimCode table with Fact table 

In reporting, you will need to use the bridge table to bring slicers and filters into context.

Hope this helps:) 

[v-venuppu]

Hi @Oimat ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[v-venuppu]

Hi @Oimat ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @Oimat ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @MasonMA @speedramps for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[MasonMA · Accepted solution]

@Oimat 

Hi, If your model is small and performance is acceptable, you can simplify and skip full normalization. Just be aware that Power BI is flexible, but this flexibility can lead to issues with ambiguous relationships, especially as your model grows or logic gets more complex.

Power BI supports this natively using composite models or cardinality,  many-to-many. You can still filter and write DAX accurately using TREATAS, CROSSFILTER, or REMOVEFILTERS.

However again, restructuring using bridge tables will improve the clarity and reliability of your model. From a performance perspective, clean dimensions and star schema help Power BI optimize filter propagation and memory usage, especially with larger datasets.

Instead of duplicating your project table 3–4 times, you can reference it in Power Query to keep things tidy. Each reference can extract a distinct list of values (like Project Code) and you build your bridge tables from there.

While this approach adds a bit of setup upfront, it pays off in long-term maintainability, accurate filtering, and performance.

Hope I've made my points clear:) 

[MasonMA · Accepted solution]

Hi @Oimat 

Please consider decomposing the many-to-many relationship using bridge tables. You can create different Dimension tables with Distinct columns like below.

From your Dim table

Duplicate the DIM query into a query with Code and create one DimCode table with unique values. 

When modelling your relationship create your DimCode table with Fact table 

In reporting, you will need to use the bridge table to bring slicers and filters into context.

Hope this helps:) 

[Oimat]

Would that really change anything performance-wise ? 

Since my projects have many multiple-choice columns, I would need to create (Project Code, Manager, Scientific expert...) I would need to replicate 3-4 times the query, which would make it very messy, it seems.

[speedramps]

I want to help you but your description is too vague. Please write it again.

You have not explained what a "Code" is of why a Project can have multiple Codes and why an Owner can have mutiple Codes.  You have also not considered a Code can can multiple Projects or Owners. Those factors will determin the relationship requirements.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). ‌‌
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.