Calculated Column using Composite Model broken in September release

[bpaynetrain]

In the August Release we had several calculated columns structured as shown below where the calculated column is created on an imported table but it refrences fields in a direct query table. 

OpenEscalations = 
calculate(
  sum(ServiceCalls[Escalated]), 
  filter(ServiceCalls,
    ServiceCalls[ID]=Devices[ID] 
    && ServiceCalls[DateClosed] = blank()
  )
)

In the August Release of Power BI Desktop this formula works properly.  I can create a table where I list devices and the total number of escalations associated to each device next to it.  After upgrading to September, the formula and visuals continue to function until any attempt to change it (even changing it back to it's former state) results in an error message "The expression referenced column [Column Name] which does not hold any data because it needs to be recalculated or refreshed."  Note that the table/column name in the error message need not be included in the DAX formula.  It's another similar formula on a completely unrelated table.  

I have a file that uses 4 of these types of formulas and once any of them are edited, all of them stop functioning.  Additionally, all measures using the direct query tables also stop functioning (all reporting the same error message referencing a table/column that doesn't necessarily have anything to do with the measure or calculated column in question). 

Attempting to refresh the entire file doesn't appear to have any impact on the issue.  The only way to get the file back to a workable state seems to be to delete all instances of calculated columns which reference direct query tables.

[v-yuezhe-msft]

@bpaynetrain,

Could you please post table structure and sample data of your tables here so that I can repro?

Also we need to know which table you use DirectQuery mode to import.

Regards,
Lydia

[bpaynetrain]

I just create a couple of simple tables to test in a SQL Server Database (SQL Server 2012 if it matters).  Here's my script:

-- create import table
CREATE TABLE dbo.IMPORT_TABLE (
	ID int NOT NULL,
	[Name] varchar(25) NOT NULL
	)
GO

-- create direct query table
CREATE TABLE dbo.DQ_TABLE (
	ImportID int NOT NULL,
	[Hours] float NOT NULL
	)
GO

-- Insert rows into Import table
insert into IMPORT_TABLE (ID, [NAME]) values (1,'Brian')
insert into IMPORT_TABLE (ID, [NAME]) values (2,'Sarah')
insert into IMPORT_TABLE (ID, [NAME]) values (3,'Dan')
insert into IMPORT_TABLE (ID, [NAME]) values (4,'Mark')
insert into IMPORT_TABLE (ID, [NAME]) values (5,'Mike')

-- Insert rows into Direct Query table
insert into DQ_TABLE (ImportID, [Hours]) values (1,1)
insert into DQ_TABLE (ImportID, [Hours]) values (1,1.5)
insert into DQ_TABLE (ImportID, [Hours]) values (1,2)
insert into DQ_TABLE (ImportID, [Hours]) values (1,0.5)
insert into DQ_TABLE (ImportID, [Hours]) values (2,3)
insert into DQ_TABLE (ImportID, [Hours]) values (2,1)
insert into DQ_TABLE (ImportID, [Hours]) values (3,0.5)
insert into DQ_TABLE (ImportID, [Hours]) values (3,1)
insert into DQ_TABLE (ImportID, [Hours]) values (3,1)
insert into DQ_TABLE (ImportID, [Hours]) values (3,1)
insert into DQ_TABLE (ImportID, [Hours]) values (3,2.5)
insert into DQ_TABLE (ImportID, [Hours]) values (3,1.5)
insert into DQ_TABLE (ImportID, [Hours]) values (4,3.5)
insert into DQ_TABLE (ImportID, [Hours]) values (4,0.5)
insert into DQ_TABLE (ImportID, [Hours]) values (4,1.5)
insert into DQ_TABLE (ImportID, [Hours]) values (4,1)
insert into DQ_TABLE (ImportID, [Hours]) values (5,2)
insert into DQ_TABLE (ImportID, [Hours]) values (5,3)
insert into DQ_TABLE (ImportID, [Hours]) values (5,1)
GO

I connect to my two tables (one as import and one as Direct Query).  When I connect I'm using the advanced option to just enter some SQL:

select * from IMPORT_TABLE

and

select * from DQ_TABLE

Note that whenever you edit the source for the direct query table (say to update the SQL to include different columns) it will default back to Import which is annoying but a different issue.

I establish a connection between Import.ID and DQ_TAble.ImportID and make it filter in both directions:

Relationship between tables

Then I go to my report and create a table visual.  I add a couple fields from my Import Table and then try to create a calculated field.  Say for instance, I want to create a calculated column that counts up how many  rows for each person have more than 2 hours:

CountOfMoreThan2Hours = 
calculate(count(DQ_TABLE[Hours]), filter(DQ_TABLE,DQ_TABLE[Hours] > 2))

Note Error Message which says this formula can't be evaluated because it references itself.

Note, that this new calculated column will also prevent any existing or new measures related to my Direct Query table from calculating.  For all practical purposes it renders the direct query table complete unusable until I delete this calculated column.  In the August version of Power BI destop this worked without issue.

[v-yuezhe-msft]

@bpaynetrain,

After performing the above process in Power BI Desktop August release, I am able to refresh the data but unable to create same calculated column in Power BI latest version, could you please try the new Power BI september release(2.62.5222.761)?

Regards,
Lydia

[bpaynetrain]

@v-yuezhe-msft

I updated to Power BI 2.62.5222.761 this morning and the issue still exists.  I originally saw experienced the issue when I updated to Power BI 2.62.5222.601 and the lastest version exhibits the same behavior.

Brian

[apugh]

Just wanted to chime in to say I'm experiencing the same issue -- thanks to Brian for reporting and to Lydia for any help you can offer!

I was able to create calculated columns combining fields from both direct query and import tables successfully when I originally switched on the composite feature in August, and they worked just fine for weeks. At the end of last week, when I opened up my file (maybe an update had just gone through, I did not get a notice) any columns or measures using fields from the direct query table were in error. As Brian experienced, the measures will work again, but only once calculated columns that reference the direct query table are deleted. This is regardless of whether the measures' DAX formulas reference those broken columns. The error message I receive on the broken calculated columns is also the same, and references itself ("The expression referenced column [Column Name] which does not hold any data because it needs to be recalculated or refreshed.") 

[v-yuezhe-msft]

@bpaynetrain @apugh,

I have reported this issue internally: CRI 84813128. Will update here once I get any feedback.

Regards,
Lydia

[v-yuezhe-msft]

@bpaynetrain @apugh,

This issue will be fixed in October release of Power BI Desktop.

Regards,
Lydia

[apugh]

Thanks so much, I see this was fixed in the desktop application after downloading the October release! But it looks like this remains an issue when reports with these types of columns are published to the service. The two visuals that rely on my calculated column have returned to functioning perfectly in the desktop version, but once published to the service, return an error similar to the one I had been getting in desktop (pasted below) prior to this fix. If you need any further details, please let me know. 

Couldn't load the data for this visual
The query referenced calculated column 'FY19 Forecast'[Percent Progress] which does not hold any data because evaluation of one of the rows caused an error.
Please try again later or contact support. If you contact support, please provide these details.