IF function not working with Measure?

Question

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")

edhans · Answer

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.

edhans · Answer

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.

domenick · Answer

Yes, 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

domenick · Answer

edhans&nbsp; 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.

edhans · Answer

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.&nbsp;If DQ was working in the service, you are done with refresh. Just schedule it. Your cloud or gateway config is already done.&nbsp;You will find Power BI is much more powerful and feature rich when you use import models. Direct Query is really for three things:Reports that need to be nearly real time.Tables that have millions and millions of records and an import is not feasibleYour dataset would be over 1GB for an import. Although I cannot image how slow the report would be if it were pulling in 1GB+ via Direct Query.&nbsp;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.&nbsp;

Forum Discussion

IF function not working with Measure?

5 Replies

Related content

Function DATESINPERIOD doesn't work properly

EARLIER Function not working

Measure not working with REMOVEFILTERS function

SUMMARIZE FUNCTION in measures

function SAMEPERIODLASTYEAR() not working