Forum Discussion
RELATED function does not work - is dual/import mode an issue or a "limited" relationship?
I have a simple measure I created as (below) that was working just fine when all of my data was from Excel files. The relationships between tables used to facilitate this measure are also included below. The Decisions table is a many-to-one relationship to my main fact table, and the fact table is then a one-to-many to the Terms table (see image below). (please note, this is my dev report, so names and such could be wonky).
This measure was working beautifully in my development environment, again, linked to Excel files on the network all in Import mode.
Now, I'm moving my report to our production envintoment where I have linked SQL server tables from two data sources; I also have some local date and index tables. Some of the larger tables are in Dual mode, but most are in import mode. I've created a .pbix and published to PowerBI.com and have a gateway running. The dataset and gateway are working just fine, and the data in the tables is exactly the same (as it is in my dev report).
I've copied the measure into my new production report, and now it's not working. I'm getting the error "The column Terms[Term Rank] either doesn't exist or doesn't have a relationship to any table available in the current context" and I don't know why. The tables are definately related; intellisense works when I type out the measure.
What's wrong? Is it something to do with the dual/import table modes, or so I now have a limited relationship or something? I'm wondering why it's not working, but also how to best fix it.
frittzy see below from the documenation on RELATED() and limited relationships. It appears that your hunch that this is due to using a dual-mode/composite model is correct.
https://docs.microsoft.com/en-us/dax/related-function-dax
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand#limited-relationships
5 Replies
frittzy see below from the documenation on RELATED() and limited relationships. It appears that your hunch that this is due to using a dual-mode/composite model is correct.
https://docs.microsoft.com/en-us/dax/related-function-dax
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand#limited-relationshipsThanks for confirming Anonymous, but can I ask why it's now a limited relationship? I HAS to be an issue with the tables/modes themselves, becuase again, when all my tables were in Import mode, the RELATED function worked just fine.
Also, how do I best resolve this... I'm still working on it and I'll figure it out, just looking for some direction as well. Thinking using RELATEDTABLE will be part of it.
Thanks,
Jordan
frittzy did you read the documentation on limited relationships that I linked? It's right there....
As for the resolution, the first question I would ask is why you can't use just import mode. Are the tables so large that DQ is truly the only option?
Wow, my bad Anonymous... I read that article before I posted, then revisisted since I clearly missed something, and then missed it again. I'm still becoming 'fluent' in PBI language. I was reading 'cross source group' as different sources (sql, orcale, etc.), not different models (which I shoudl have known is what Composite meant). <sigh>
Anyway, that's a good question. My tables are going to be <20,000 records per table, which I'll have 6-7 "dual" mode tables now. Now my mind is going to "when should I use DQ/dual vs. Import"? I've researched that a bit and only with with Dual so working on PBI Desktop was faster. Considering I have the gateway running just fine, what should I be considering then when thinking about which model modes I'm using.
