Forum Discussion
Power Query Merge or DAX relationship connection multiple fields
Hi,
I am trying to connect two tables together however I need to connect them on multiple fields ProductId, WeekOffset and Location.
I can do this by merging in Power Query but it takes quite a while as table one has circa 3 million records and the second table 30 million.
Would it be better and quicker if I concatenated the fields needed and connected them in the DAX relationship connection?
The combination of the 3 fields will create a unique indentifier in each table.
Thanks,
Mark.
2 Replies
Hi Anonymous ,
I would like to suggest you check out this Video: Introduction to Data Modeling for Power BI.
Which model to use depends on your needs. They have their own advantages and disadvantages. Some concepts are explained in the video, such as:
1. Working with a single table
o In Excel, you work with a single table
o As simple as it is, it is already a data model
o It comes with several limitations
• Number of rows: less than 1 Million
• Speed and memory usage are not optimal• Can only perform basic calculations
o The limit on size becomes a limit on the data model
2. Granularity
o Granularity is the level of detail of your table
o The more the columns, the higher the granularity
o Higher granularity
• More detailed information
• More powerful model• Increase in the number of rows
o Lower granularity
• Faster and smaller model• Less analytical power
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hey Anonymous ,
you have to provide more details about the nature of both tables.
Not sure if we have the same understanding of unique identifier, sure sometimes it becomes necessary to combine different columns in a table to create a unique identifier. I understand a unique identifier as being able to uniquely identify a row in a table. For this I'm wondering if the combination of the 3 columns in the larger table also creates a unique identifier or allows to establish a relationship between the smaller table (3M rows) on the one-side of the relationship and the larger table (30M rows) on the many-side of the relationship.
Sometimes an order table that by itself is on the many-side of relationships can be on the one-side of a relationship, e.g. to an order detail table that contains information about each line of an order. This scenario most often leads to a denormaliztion of both tables into a single fact table, if one is following a star schema approach.
This is a great book about data modeling even if this not revealed by the title of the boo: https://www.amazon.com/Analyzing-Power-Pivot-Business-Skills/dp/150930276X/ref=sr_1_6?keywords=marco+russo&qid=1576938104&sr=8-6
Regards,
Tom
