The semantic models contain calcualted tables/column that refer to remote tables?

[danextian]

Hello,

I normally don't post as I am able to resolve most of my Power BI queries on my own but this one is a puzzle to me. The error in the screenshot is normally what I would get if I created a calculated table or column referencing a direct query table/column. My semantic model is composite but all the columns in the calculated table formula are from imported tables. Now, if I removed KEEPFILTERS the warning would go away. I can refresh just fine in the service in a Pro workspace but this is an extra step everytime i publish from Desktop. 

[ThxAlot]

Under DirectQuery mode, DAX measures are transformed by formula engine of VertiPaq into sqls to retrieve necessary data from remote models. There exist some limitations on certain DAX funtions; complex measures are advised to avoid as well.

[tharunkumarRTK]

@danextian 

I guess the column within the KEEPFILTERS() was taken from a remote table (pulled from an external semantic model). Is it? 

Even if that is the not the case, the dax query with this measure would need to observe the filters from remote tables as well. I believe that is reason for this warning message. 

I understand that you connected to an existing semantic model and imported other tables and converted live connectivity to direct query storage mode

Just FYI, Power BI has released a new update and now you can refresh your semantic model even though you have calcualted columns and tables referring to remote tables 

check out this link 

I raised a microsoft case for this feature, finally msft heard my voice
https://powerbi.microsoft.com/en-us/blog/power-bi-may-2024-feature-summary/#post-27048-_Toc167109016
just kidding

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[danextian]

This is a composite semantic model but every column referenced in that calculated table are from imports. I do have direct query tables from an external semantic model but they are all fact and not dimension tables and certainly they aren't in the formula. The performance analyzer would have also  shown a row for direct query but there is none.

[tharunkumarRTK]

Ok, can you share the pseudo code of your DAX query? you can copy it from the performance analyzer

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[danextian]

This one is from the calculated table:

// DAX Query
DEFINE
	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL('AverageByDayOfWeek2'[Name of Day], "IsGrandTotalRowTotal"),
			"SumAverage_Rev", CALCULATE(SUM('AverageByDayOfWeek2'[Average Rev]))
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'AverageByDayOfWeek2'[Name of Day], 1)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	[IsGrandTotalRowTotal] DESC, 'AverageByDayOfWeek2'[Name of Day]

This one is from viewing the same measure without using a calculated table and yet averaged based on the name of day.

// DAX Query
DEFINE
	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			'Dates'[Date],
			'Dates'[Name of Day],
			'Dates'[Day Sort],
			"Legacy_Sites_Tracker_Revenue", '__MEASURES'[Revenue]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(501, __DS0Core, 'Dates'[Date], 1, 'Dates'[Day Sort], 1, 'Dates'[Name of Day], 1)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'Dates'[Date], 'Dates'[Day Sort], 'Dates'[Name of Day]

Performance analyzer does show a row for direct  query

[tharunkumarRTK]

@danextian 

I replicated your scenario but could not reproduce the warning message. I followed below steps:

1. Created a new PBIX file and connected to an existing semantic model hosted in power bi service 

2. Imported another table from excel after converting the storage mode from live to direct query. 

3. Created a calcualted table and refered the columns from import mode based table. 

4. Placed the columns from calculated table into a table visual and looked into performance analyzer, I did not see direct query portion. 

I was able to publish and did not get any warning message. Let me know if I missed any step. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[danextian]

Did you use keepfilters from a dimension table?