Related() issue

[Anonymous]

Hi All, 

I'm kind of new to Power BI, and I am having some issues with the related() function, as I am trying to relate a column to a different sheet but get the error "The column 'Sheetname[Column name]' either doesn't exist or doesn't have a relationship to any table available in the current context." I have made sure that there are no duplicates or blanks in both sheets. Also I noticed that the relationship cannot be changed from many to many to other relationships as I get the error "The cardinality you selected isn't valid for this relationship".

Can anyone please help me in knowing the reason behind this? would be much appreciated!

Haya

[AntrikshSharma · Accepted solution]

RELATED doesn't works because DAX is based on Expanded tables concept, where each table on the one side of the relationship expands to the related table on the many side, just like LEFT join in SQL, and RELATED only allows you to access columns of an expanded table, but table expansion doesn't happens in case of many to many relationships. so you will have to either create a bridge table, or create dimensions properly or you use something like this :

=
MINX ( RELATEDTABLE ( Table ), Table[ColumnName] )

https://www.sqlbi.com/articles/expanded-tables-in-dax/

[Anonymous]

Thank you! This actually worked 🙂 

[MattAllington]

Related is not a very common function to use if you build a good data model and good DAX. There is a lot to learn. Unlike Excel, the relationships are normally modelled in the model view and most of the rest can work from there without using the related function. But of course it depends what you are trying to do. 

my article here should help you get started. https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/