Related function error

[IgorGershenson]

I am completely lost with my Related function. I have a field in my Tasks table that is a calculated column. The formula is simply a related function that looks up opportunity create date. I keep getting the error above about this field. Typically, you get this type of error when the look-up table the related function goes to has duplicate IDs. I know for a fact my look-up table does not, and yet I still get this error. Any ideas?  I only get this error when I refresh my queries. I don't get any errors when I look at the query in the data view. Really odd.  Also, my look-up table is coming from Salesforce in case it is relevant.   One last thing: In the query  creating the look-up table I removed all blanks and errors as well as dups for the ID used to join the look-up table to the Tasks data

[parry2k]

@IgorGershenson That is super strange, source of the data has nothing to do with it. Well, very hard to tell why that was the case.

[IgorGershenson]

Indeed. Well,  my theory is when you have too many links to SF directly, Power BI starts malfunctioning, but, yeah, it is a bit of a mistery. 🙂

[parry2k]

@IgorGershenson this is what I will do, remove the relationship, and see if refresh happens, which will. Then put id and count of id from a lookup table in a table visual, sort by count, and check if there any id has count which is not 1, and or any id which is blank. From M script it looks like there should be no blank and no duplicate but we have to make sure that is the case.

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[IgorGershenson]

Hi, it turns out the issue had to do with connecting to SF directly. My look-up table was grabbing data directly from SF Production. Once I created the same look-up table in Dataflow and connected to it through Power BI desktop, the problem vanished. Strange. 

[parry2k]

@IgorGershenson the reason you are probably getting errors is because of the quality of the data, maybe your lookup table doesn't have unique value on the column in which you have the relationship.

Check your raw data to make sure there is no duplicate issue?

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[IgorGershenson]

Thanks, but here is my M code for the look up table. As you can see, I get rid of dups by using Group By feature as well as removing dups directly.

let
Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48, CreateNavigationProperties=true]),
Opportunity1 = Source{[Name="Opportunity"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Opportunity1,{"Id", "CreatedDate"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Id] <> null and [Id] <> ""),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Id"}),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Id"}, {{"CreatedDate", each List.Max([CreatedDate]), type datetime}})
in
#"Grouped Rows"

[parry2k]

@IgorGershenson RELATED function only works when you are adding column from one side relationship. How is your relationship setup?

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[IgorGershenson]

The join is many (Tasks) to one (lookup table). When I enter the related function I get no error. I only get the error above when refreshing queries

Here is the screenshot of the related function. No errors appear in the data view