Link tables in Power BI by two columns

Power BI is not always friendly to BI developer and you need to do some work around to get what you need.
Here is a simple case:

The Problem:

We have two tables in Power BI Desktop, which we need to link, but it happens they have to be linked by two columns.

The Case:

Simplest case is to create two Queries to a "master" table in any SQL Server. Call them "Partitions" and "Indexes":

 

Here are these simplest queries:

-- Partitions SELECT * FROM sys.dm_db_partition_stats; -- Indexes SELECT * FROM sys.indexes;

If we go now to a model tab and try to link tables by "object_id" column . . .

We will get a message that we can build only "Many-2-Many" relationship and if we want to do a "One-2-Many" we need to have a column to be unique on one side:

The problem is that we can't select two columns to link those two tables to establish relationship.

The Solution:

Do a right click on "Indexes" query in the "Fields" tab and select "New Column":

 Specify following formula in the "New Column" box at the upper left:

Index_Key = POWER (2,32) * Indexes[index_id] + Indexes[object_id]

Like this:

Create new column in the "Partitions" the same way as for "Indexes":

The formula for Partitions will be only slightly different:

Index_Key = POWER (2,32) * Partitions[index_id] + Partitions[object_id]

Will look like this:

As the result, you will see the New columns in the modeling view, where you can click on "Manage Relationship" button in the menu.

For the new relationship specify following:
1. First table: "Indexes"
2. Second table: "Partitions"
3. Select the very last column "Index_Key" in both tables
4. Specify cardinality as "One to many (1:*)"
5. Apply security filter in both directions (optional)
6. Hit OK

Now you are supposed to get such a beautiful "One-2-Many" relationship:

Possible caveats:

In my example "Index_id" numbers are very small.
If your calculated number hits a value bigger than "POWER (2,53)", which is the current Power BI Desktop application limit for the Whole numbers, in numeric form the limitation number is: "9007199254740992"
*That is applicable for version of: 2.73.5586.661 64-bit (September, 2019)

In that kind of situation you can use a string concatenation formulas like this:

Index_Key = Indexes[object_id] & "-" & Indexes[index_id] Index_Key = Partitions[object_id] & "-" & Partitions[index_id]

*The "-" symbol is very important, because you can hit the situations when two numbers can possibly generate a similar string like "1&11" produces "111" and "11&1" also produces "111", which won't allow you to establish the uniqueness.