RELATED function not working

[Anonymous]

Hello,

I am trying to calculate net work days between a starting and ending point but am having trouble. I am following the solution in this thread:

https://community.powerbi.com/t5/Desktop/DATEDIFF-Working-Days/td-p/130662

but am having some difficulties. I have one file that has a list of dates from December 2018 to December 2021, and then I have a file with maybe 14 lines (just holidays in 2019). I have managed to create a relationship between the two "Date" columns and have the WEEKDAY function figured out, but when I attempt to use the RELATED function to make the holiday column, this appears.

Here is the relationship I have, as well as the formate of my second set of data with all the holidays. My first set of data is the first column in the picture above; just a list of dates.

Thanks

[Anonymous]

RELATED works only from Many to 1 side.....u have Many to Many between tables

[BekahLoSurdo · Accepted solution]

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.

[Anonymous]

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!