Powerpivot multi column relationship

Relationship in Power BI with Multiple Columns | RADACAD

powerpivot multi column relationship

Hi all i want to make a multiple key relation in Powerpivot Create a calculated column in both tables by concatenating the two fields (I suggest. You should create a key in each table that concatenates the 2 fields together. This could be done in a calculate column eg: = LATITUDE & "-" &. Power Pivot does support Many to Many relationships, but it requires a bit of DAX magic. Select at least one column that contains only unique values. saying the same item showed up multiple times on both sides eg.

powerpivot multi column relationship

Why would you need Multiple Relationships at All? The most common scenario I have come across is when you have two date columns in your data table eg Order Date and Ship Date and you want to join both of these columns to the Calendar table.

If there were only 1 date column, then you would typically set up your data like this shown below. When you do this, you will get the following behavior as shown below. Note there are now 2 relationships, but one of them the second one added is a dashed line.

If you hover the mouse over this new relationship with the dashed line and then right click, you will see a menu pop up as shown below. This is really the only information you will see in Power Pivot that refers to active and inactive relationships.

If you change the inactive relationship and make it active, then the active relationship will automatically switch to inactive. How to Use Inactive Relationships For this blog post, I will keep the relationship between Sales[Order Date] and Calendar[Date] as the active relationship and leave the other relationship as inactive.

Many-to-Many Relationships | Power Pivot | Tiny Lizard

The formula [Count of Orders] is simply as follows So now the problem is, how to count the orders that shipped on each day? DAX allows you to override the active relationship and use the inactive relationship in your formulas instead. The relationship MUST exist in the data model and be set as inactive for the above to work. The Pivot Table now will look like this, showing for each date, how many orders were taken and how many orders were shipped. This would mean that you have 2 calendar tables and can use the one you need for the problem at hand.

Which approach you use is really down to personal preference and also how you are planning to report on your data. In the Pivot Table above, you can see that it is easy to look at any date and see how many orders came in and how many orders were shipped out.

powerpivot multi column relationship

However the orders counted in these two columns are not the same orders orders coming in on any date will ship either the next day or 2 days later in this test data. The only problem with this approach is that the Shipped Date is coming from the Sales table.

In a Data Model, you cannot create a table relationship if the key is a composite key. Other relationship types are not supported. You can do this before you import the data, or by creating a calculated column in the Data Model using the Power Pivot add-in.

However, you can use DAX functions to model many-to-many relationships. A self-join is a recursive relationship between a table and itself. Self-joins are often used to define parent-child hierarchies. In other words, the following set of relationships is prohibited.

powerpivot multi column relationship

Automatic detection and inference of relationships in Power Pivot One of the advantages to importing data using the Power Pivot add-in is that Power Pivot can sometimes detect relationships and create new relationships in the Data Model it creates in Excel. When you import multiple tables, Power Pivot automatically detects any existing relationships among the tables. The detection algorithm uses statistical data about the values and metadata of columns to make inferences about the probability of relationships.


Data types in all related columns should be compatible. For automatic detection, only whole number and text data types are supported. For the relationship to be successfully detected, the number of unique keys in the lookup column must be greater than the values in the table on the many side.

In other words, the key column on the many side of the relationship must not contain any values that are not in the key column of the lookup table. For example, suppose you have a table that lists products with their IDs the lookup table and a sales table that lists sales for each product the many side of the relationship.

To have Excel detect the relationship, you need to first update the Product lookup table with the IDs of the missing products.

powerpivot multi column relationship

Make sure the name of the key column on the many side is similar to the name of the key column in the lookup table. The names do not need to be exactly the same. For example, in a business setting, you often have variations on the names of columns that contain essentially the same data: The algorithm detects similar names and assigns a higher probability to those columns that have similar or exactly matching names.

Therefore, to increase the probability of creating a relationship, you can try renaming the columns in the data that you import to something similar to columns in your existing tables.

powerpivot multi column relationship

If Excel finds multiple possible relationships, then it does not create a relationship. This information might help you understand why not all relationships are detected, or how changes in metadata--such as field name and the data types--could improve the results of automatic relationship detection. For more information, see Troubleshoot Relationships.