Forum Discussion
RELATED function not working
Hi Anonymous ,
The first step would be to remove the blank rows in your Holi table. You can do this in your source data or via Power Query:
= Table.SelectRows(Source, each [Date] <> null and [Date] <> "")
Then delete and re-create your relationship between the two tables so that it is 1-to-many instead of many-to-many.
This should allow the RELATED function to operate as expected.
Resolver IVHi Anonymous ,
The first step would be to remove the blank rows in your Holi table. You can do this in your source data or via Power Query:
= Table.SelectRows(Source, each [Date] <> null and [Date] <> "")
Then delete and re-create your relationship between the two tables so that it is 1-to-many instead of many-to-many.
This should allow the RELATED function to operate as expected.
Thanks for the reply, this did indeed solve my problem. I was hoping you could explain to me why exactly did deleting the rows/making the relationship many to one fix my issue? And why does the related function only accept one to many relationships? Thanks!
- BekahLoSurdo
Hi Anonymous,
Two good questions.
1.) Speaking from experience, you can not have any nulls (not even one distinct null) on the "one" side of a one-to-many relationship in Power BI. Even adding one blank row will automatically turn it into a many-to-many relationship (I tested this because I was curious).
In large datasets, sometimes it's hard to see that there is a blank row and I discovered this fact through trial and error: I had a table that I knew had distinct values but was generating a many-to-many relationship with another table. Once I removed blank rows, it worked correctly.
2.) The RELATED() function by defintion (https://docs.microsoft.com/en-us/dax/related-function-dax) returns a single value so it can not work on a many-to-many relationship because the referenced row value has the possibility of corresponding to more than one related value for any given column.
Hope this helps!