Creating Multiple manage relationships at ones

[Bensheepero]

Hi everyone,

I have an issue where I have over 50+ tables in my report. I need to create a link between the date/time columns of these tables and my lookup date and time tables respectively. Now I can do them 1 by 1, but the report is so large that it takes approximately 10 seconds for each. Is there any way with which (perhaps Tabular editor) that I can write a script that does this automatically for me for all the tables. FYI - The automatic relationship option in power bi is not a feasible option for me. 

[MFelix · Accepted solution]

Hi @Bensheepero ,

You can use the TMDL script options to generate your relationships in Power BI desktop. If you do one of the relationship than use the TMDL script to get the code and use chat GPT or another AI tool.

The scripts on TMDL are basically like this:

createOrReplace

	relationship 22d0e674-7082-40ae-d047-70859390746a
		fromColumn: Table.Column
		toColumn: CalendarTable.Date

So using this you can do a similar AI promtp:

Can you generate the TMDL for relationships based on the srcipt below, I want to relate the table Calendário and column Data with the following tables:
Table 1 column Data
Table 2 Column Data

Don't forget to generate a single script and also include specific guid for the relationship

TMDL script example

createOrReplace

	relationship 22d0e674-7082-40ae-d047-70859390746a
		fromColumn: Table.Column
		toColumn: CalendarTable.Date

Then you just need to copy paste and run it.

[v-sgandrathi]

Hi @Bensheepero,

Thank you @MFelix, @Mvsainathareddy and @Royel for your responses to the query.

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @Bensheepero,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

Please let us know if there’s anything else we can do to help.

Thank you.

[v-sgandrathi]

Hi @Bensheepero,

Just looping back one last time to check if everything's good on your end. Let me know if you need any final support happy to assist if anything’s still open.

Thank you.

[MFelix · Accepted solution]

Hi @Bensheepero ,

You can use the TMDL script options to generate your relationships in Power BI desktop. If you do one of the relationship than use the TMDL script to get the code and use chat GPT or another AI tool.

The scripts on TMDL are basically like this:

createOrReplace

	relationship 22d0e674-7082-40ae-d047-70859390746a
		fromColumn: Table.Column
		toColumn: CalendarTable.Date

So using this you can do a similar AI promtp:

Can you generate the TMDL for relationships based on the srcipt below, I want to relate the table Calendário and column Data with the following tables:
Table 1 column Data
Table 2 Column Data

Don't forget to generate a single script and also include specific guid for the relationship

TMDL script example

createOrReplace

	relationship 22d0e674-7082-40ae-d047-70859390746a
		fromColumn: Table.Column
		toColumn: CalendarTable.Date

Then you just need to copy paste and run it.

[Royel]

Hi @MFelix  As far i know the GUID id needs to be unique, and TMDL can not generate this automatically. 

relationship 22d0e674-7082-40ae-d047-70859390746a

 Any thoughts? 

Thanks 

[MFelix · Accepted solution]

Hi @Royel ,

The GUID is a unique value that can be any text value it does not have to be a specific format, in some type of fields it can even be deleted and the model will continue to work, in this case you need to add some text.

Has I refer you need to use the prompt in AI to give you specific GUID, but if you do it manually you can just add any GUID you want see the  gif below:

However in the TMDL viewer for specific object lineage tag that is also a GUID can be automatically generated. And depending on the type of object even repeated, for measure you need to have specific GUID (lineagetag) for Columns you can have the same.

Based in this you can have several options:

• Use AI to generate the several GUID
• Generate them in a random excel file
• DO a combination of table names and columns

Has reference the main thing you need to be carefull is that they are unique in the same type of element in this case relationship.

[Royel]

Thanks a lot, you help me to learn new technique. 

[Mvsainathareddy]

Hi @Bensheepero 

Use Tabular Editor from Power BI Desktop
Even though you're in Power BI Desktop, you can still use Tabular Editor 2 or 3 via the External Tools ribbon:

Open your report in Power BI Desktop.
Go to External Tools → Tabular Editor.
Run the script to auto-create relationships.
This is the most efficient method and works directly on your Power BI model.