How to calculate a metric despite direct query limitations

[tylersbrown01]

I build line charts for manufacturing companies to help them keep their processes under control. If a variable value trends above or below a SPEC LIMIT (just a solid line defining the limits of an acceptable measurement for a given variable), then manufacturing is stopped and problem is corrected. Now, they want me to predict these issues before they occur.One way to predict to do this is to calculate Cp (process capability) 

The forumula for CP is: Cp = ([upper spec limit - lower spec limit]) / ([STD DEV] * 3)

I'm having trouble calculating this formula in DAX under the direct query connection model. 

Here is how my schema is set up (only two tables requird to understand this) 

I'm calculating CP as a measure inside fact_product_variable, which contains many values for each variable listed in the dim_variable table. dim_variable contains the USL and LSL values I need. To make this calculation work, for each variable name, I need to calculate a CP value.

This is the DAX formula I attempted to write.

Cp2 = SUMX (
	fact_product_variable,
	CALCULATE(
		SUMX (
			dim_variable,
			(dim_variable[usl] - dim_variable[lsl]) / ([StdDev] * 3)
		)
	)
)

I'm not sure if this is correct or not but it was my best shot. After typing the formula, I get an error saying Function 'SUMX is not supported in this context in DirectQuery mode.

Is it even possible to calcualte Cp under these constraints? If so, what do I do? Thanks!

[v-huizhn-msft]

Hi @tylersbrown01,

By default, limitations are placed on DAX expressions allowed in measures when you connect the SQL Server in Direct Query mode. For bypass this limitation, you’d better select File -> Options and then Settings -> Options -> Direct Query, then selecting the option “Allow unrestricted measures in Direct Query mode”. When that option is selected, any DAX expression that is valid for a measure can be used.

AS I tested, the SUMX function works fine in Direct Query mode and get desired result after settings. Please set up your Power BI desktop and check if it still have the issue. For more details, please review this article.

If you have any other question, please feel free to ask.

Best Regards,
Angelia