Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'd like to compare a field to a calculated field, but I'm getting an error I don't quite understand:
"A calculated column or RLS expression on a DirectQuery table cannot reference tables from a different data source"
Here is what I'm trying to accomplish:
I want to compare a field [DATE] in my table to a specific [DATE] field that has been determined in another calculation.
In the other calculation, I basically needed to find the MAX [DATE] where an associated field was "Yes." I essentially want to be able to create a new field that follows the logic: If([MostRecentlyApprovedDate]<=[DATE],"Yes","No")
Direct Query models have a number of limitations and should be used sparingly. I think what the error is saying is your [MostRecentlyApprovedDate] measure and [Date] measure are from two different sources, and you cannot do that with a direct query model. You can with an import.
Can you confirm the tables used in each of those measures are, in fact, from different sources?
See this for limitations on DQ. It may be this one that is biting you:
"Limitations are placed on DAX expressions allowed in measures to ensure that queries sent to the underlying data source have acceptable performance."
It may cause performance issues to have the model try to send two different direct query commands to two different sources and combine into a single measure for comparison like you are trying to do.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYes, the measure [MostRecentlyApprovedDate] relies on a data field that is in another table. I can't really avoid that; it's a relational database.
One workaround, I suppose, would be to join the tables before importing them. This would have been easy to do from the beginning, but I put a little too much faith in PowerBI. To do it now, would mean rebuilding everything from scratch. So it would be nice to be able to not have these
It isn't another table. It mean another source. So two tables from one database are one source (or should be) but two tables from two different databases are two sources, and you cannot join those in Power Query if you are in Direct Query mode.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans Yeah, the error is really frustrating. I'm only using one source for this entire report. It's all finance data, so it's all in one place. I decided to just switch over to Import instead of Direct Quiery, and it seems to be working now. I'll just have to figure out refreshes later.
Interesting. You might not have an issue with a join in PQ if it is from the same source, but once it is in the DAX model, it considers the two tables a different source.
If DQ was working in the service, you are done with refresh. Just schedule it. Your cloud or gateway config is already done.
You will find Power BI is much more powerful and feature rich when you use import models. Direct Query is really for three things:
I have dozens of reports here, and only 3 are direct query, and its limitations frustrate me each time. For example, you can merge tables (join) but you cannot append/combine tables. Plus there are further limitations in DAX as you discovered. But these three reports are pretty simplistic but they need to be real time.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |