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.
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!
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!