How to use related with a many to many relationship?

[prachi-jain-842]

I am using power BI desktop and I need to get a related coloumn in a many to many realtionship.

I have used the normal related syntax and the solution provided in the power bi community already but it does not seem to work for me. Any help will be appericited. 😉

[v-luwang-msft · Accepted solution]

Hi @prachi-jain-842 ,

1.RELATED
Function: Match the data in the dimension table to the fact table, that is, find the data at one end along multiple ends, but only if the two tables have established a relationship
Syntax: RELATED (column name)
Parameters: The column name is the column in the dimension table
Return: a column
Note: only used for calculating columns

2.RELATEDTABLE
Function: Match the data in the fact table to the fact table, that is, along one end of the data to find multiple ends of the data
Syntax: RELATEDTABLE (table)
Parameters: A table that is a fact table
Return: a table
Note: only used to calculate the column
Here comes the problem, the one table returned by this function will be wrong if you calculate the columns, yes, so to avoid errors, you have to aggregate the tables returned by this function

3. LOOKUPVALUE
and excel vlookup function is most similar to the related but lookupvalue, you can make a multi-conditional query, and the two tables do not need to establish a relationship
Syntax: LOOKUPVALUE (results, find column 1, find value 1, find column 2, find value 2, ...)
Result column: the name of the column that returns the result, usually the result column to be found in the dimension table
Lookup column: the column to be looked up, cannot be an expression
Lookup value: the content of the lookup
Because it is a multi-conditional query, the latter parameters are the same as the second parameter and the third parameter, but must appear in pairs, multi-conditional query must have a pair of unique value conditions, otherwise it is not successful
Return: a value
Note: the role of the calculation column, if not meet all the conditions of the value, return blank (); if all the conditions to meet the value, then return the corresponding value; if not equal, then return an error

RELATED vs LOOKUPVALUE – which one to use? (DAX – Power Pivot, Power BI)

Wish it is helpful for you!

Best Regards

Lucien

[selimovd]

Hello @prachi-jain-842 ,

you can use the REALTED function only to a one-side. So in a many to many relationship you cannot use RELATED at all.

The function to get the information for the many side is RELATEDTABLE().

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi

[prachi-jain-842]

Thank you for your response. Can you please tell what can I use to achieve the same functionality as realted in many to many realtionship?

[selimovd]

As I said, you can use RELATEDTABLE() to get the information from another table.

But you cannot exactly do the same as with RELATED. The reason is if you are on the many side you can of course get the single information on the one side. For example your many side are products and the one side is product category. Of course for each product you can get the single connected product category.

Compare now if for example your many side are products and the other many side is product colors. How would you get 3 different product colors in a single field. So you can say amount of productcolors available for this product or the min or max product color. But you cannot get many results in one field.

The question is what do you want to do? If you give more specific information I can help a little better 🙂