Related function not working

[Anonymous]

Hello everyone,

I have looked at many posts related to this issue but have not been able to find a solution. 

We have a 1:* relationship between 2 tables. The table on the many side of the relationship, tableMANY, is using direct query and the other, tableOne, is using import. In the table on the many side of this relationship, I am trying to add a column which will use the RELATED() function but keep getting the error "The column either doesn't exist or doesn't have a relationship to any table available in the current context". Our initial formula is a lot of nested IF statements so I thought it would be good to see if I am able to pull in just the field so now my formula is just RELATED(tableOne[date field]) but I am still getting the same error. 

I have seen many posts suggesting to use RELATEDTABLE() function but I don't think we should need to use that for this case as I am trying to pull the data from the one side of the relationship. If I try to go down the path of using a CALCULATED function (ex: trying to filter and grab max value), I get an error saying "Function 'CALCULATE' is not allowed as part of a calculated column DAX expressions on DirectQuery models". 

Does anyone have any ideas on how to solve this? Is there some issue around using RELATED() with direct query + import tables?

[ToddChitt]

To be honest, I am NOT surprised that this is a limitation in Power BI. 

Think about it. 

It's like if you were going to bake a cake and you go to the store to get eggs, milk, and flour. You could ask the store clerk, "how much are your eggs?" and they would tell you BECAUSE THE EGGS ARE IN THE SAME STORE. 

But if you ask, "How much are the eggs in the store across the street?" The clerk can't answer that question.

IMPORT mode is data IN THE SAME STORE. 

Direct Query is data IN THE STORE ACROSS THE STREET.

[Sjaak_Molotof]

That not a very helpfull remark.

This is what I found which is a lot more helpfull:

1. The current relationship is not active.
2. The current relationship is a many-many relationship or limited relationship.
3. The current relationship is configured with an incorrect column.
4. The current relationship is a many-one relationship and you are trying to use the RELATED function in the Lookup table, not in the Fact table.

[joglidden2]

I'm having the same issue. It's very dissapointing that PBI can't handle this situation. 

[v-jingzhang]

@Anonymous 

The error "The column either doesn't exist or doesn't have a relationship to any table available in the current context" occurs because of the limitations of DirectQuery. Currently, a calculated column on an import table can refer to other tables, but a calculated column on a DirectQuery table can refer only to columns on the same table. So when you try to create a calculated column on the many-side DirectQuery table, it cannot relate to columns on the one-side table and you see the error.

Additionally, calculated columns on a DirectQuery table are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions. Functions that aren't supported aren't listed in autocomplete when authoring the DAX for a calculated column, and would result in an error if used.

Reference:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#limitations-and-considerations

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#modeling-limitations

[ToddChitt]

If you are not sure if Direct Query vs Import is causing an issue, try switching BOTH queries to Import and try it. 

Basically, if you are on the MANY side of a 1-to-many relationship, you should be able to create a calculated COLUMN with the RELATED() DAX function as long as there is an active relationship line between the two tables.

[Anonymous]

Hi Todd,

Thank you very much for this information. Here are the details of the relationship:

So I am trying to create the calculated column in the CVCS table. When I just try to do a simple RELATED() function without any nested IFs, I still get an error: 

I'm not too sure where to go from here. Per the original designer of the report, we need to use DirectQuery as it is a HANA data source so they do not want to import the data. However, I have been thinking of trying to import the data on my own to see if that does fix the issue but it will be a bigger change. 

[ToddChitt]

Since Power BI is an in-memory tool, when you create a calculated column like you are trying (and it looks like everything is done correctly here) then Power BI will go through every record in the table, resolve that function to get a value, then push that resulting data up into memory.

But since the table it is goin against is Direct Query (is that right, or is it the other way around?) it may NOT be able to resolve the function. 

Try a simple test with both tables, with limited data, doing Import. Then try the same data but with Direct Query. The results of those tests should tell you something.