Getting "#ERROR" from Related() even if there is relationship

[Rachel_tsao]

Hi Experts,

I added a new column name [Check Item] = RELATED('Mapping - Expense'[TM1 Account]) , want to get the first [Item] from another table 'Mapping -Expense' , but it getting "#ERROR" (image 1), it seems the Many-Many cardinality caused this issue(image 2), anyone know how can I get the first [Item] from table 'Mapping - Expense' as output? thanks!

image 1

image 2

Thanks,

Rachel

[v-yilong-msft]

Hi @Rachel_tsao ,

Image 2 is just a warning and not a bug, but I would recommend that you do not use many-to-many relationships to build relationships as they can lead to ambiguous and complex models that are difficult to use.
I have created two similar tables based on your description.

I think you can use Index in two tables.

Finally you will get what you want.

Column = RELATED('Mapping-Expense'[Item])

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rachel_tsao]

Hi v-yilong,

Thanks for your advise, however currently "TM1 Account" is simultaneously linking to multiple different data requirements, change the column setting or relationship might impact to other reports, so I would like to avoid this option. Therefore, I was wondering if it’s possible to return just first "Item" record while still maintaining the many-to-many scenario.

Thanks, Rachel

[v-yilong-msft]

Hi @Rachel_tsao ,

I don't think it's realistic to want to maintain a many-to-many relationship in this .pbix file, and it seems that the best option at the moment is not to use a many-to-many relationship, but rather a one-to-many or many-to-one relationship, for many-to-many relationships I think you can check out this document, maybe it can help you: Many-to-many relationship guidance - Power BI | Microsoft Learn

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rachel_tsao]

Hi Yilong Zhou,

Understood, thank for the valuable adivse, I will try few more way to not change the current relationship, if there is no better solution in the end, I will try your suggedtion to change to one-to-many relationship .

Thanks,

Rachel