Bridge Table

[Earosenfeld]

I am trying to create a Bridge table with multiple unqiue columns but I am having difficulty, I have resulted to creating 4 indivual bridge tables.

I first tried to create the Bridge table by appending my queries and removing unwated columns then removing duplicates. However when I removed duplicates I removed unique values from other columns as you can see below

Then I tried to create a Bridge table by create a new table and adding distinct columns with DAX, however I am having issues adding multiple unqiue columns with the dax code.

Does anyone know how to create this Bridge table with multiple unqiue columns?

[fkapps]

My question is around the idea of "Bridge' or "Junction" tables in Fabric.

Are Fabric Warehouses built to use different schemas to bridge or join tables in other schemas? In other words, are Warehouses in Fabric/OneLake built to handle the use of multiple schemas when establishing relationships between tables in a Fabric Semantic Model?

As per my current experiments, the answer is that they are not built to do so. My experiments indicate that Junction or Bridge tables necessarily must be in the same schema as the tables they join. 

My experimental findings are that junction tables must belong to the same schema as the tables they intend to join. I would be interested in other evidence in support or contrary to that. I have screenshots and I can provide further evidence to support that concept.

My hypothesis, which I'm hoping can be refuted or confirmed is this:

In Microsoft Fabric Warehouses, if you are going to use Semantic Models to build Reports in PowerBI, all tables with relationships must exist within the same database schema.

In other words, a table in the schema `junc` CANNOT be used to link two tables in the `dim` schema. If a relationship exists between tables [dim].[Store] and [dim].[StoreAgent], a third table establishing a relationship between those two tables must remain within the same schema, namely [dim].

Thanks for your consideration of my question and any response you may have. I look forward to learning more about dimensional database design as well as the technology of Microsoft Fabric. -Stephen

[mwegener]

Hi @Earosenfeld,

is your problem solved?

If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[Earosenfeld]

Hi,

the problem is still not solved. Removing duplicates can remove unique values as well.

For example

if two columns look like

1        a

1        b

2        c

2        d

3        e

3        f

If I highlight both no duplactes would be removed, If I highlight the one with duplicated column, unqiue values would be removed from the other. 

When I want to slice two data sources by the same field, a many to many relationship does not filter correctly when there are multiple slicers in my experience. I need a many to one, and the only way I can do that is with a unique column. 

I was able to accomplish this by copying the query multiple times and remove all other columns besides the one I want to filter by, then removing all duplicated values. 

Although I really think there should be an easier way.... dont you?

[mwegener]

Hi @Earosenfeld ,

can you share a screenshot of your data model view?

[Earosenfeld]

[mwegener · Accepted solution]

Hi @Earosenfeld ,

these are not bridge tables but dimension tables and therefore completely correct.

The question is, are there any master data tables in your system from which you can load the unique values for your dimension table?

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

[Earosenfeld]

Ahh I see. I can load the unique values from a master data table. However I prefer not to create another data source if I don't have too. I guess this is the best way to go about it. Thank you for the clarification. 

[v-juanli-msft]

Hi @Earosenfeld 

Check the solution suggested by mwegener, select two columns and remove duplicates.

If problem still occurs, please share more details here, including why you need to create the bridge tables.

In Addition, you could refer to the following:

Create and manage relationships in Power BI Desktop

Bridging Table - Relationships

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mwegener]

Hi @Earosenfeld ,

mark all columns and then perform the step remove duplicates.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.