Forum Discussion
Calculated column for direct query
I have View from direct query ,based on two columns i'm calucating percentage using the below formula
Percentag =
var dr=CALCULATE(sum('ABCR_Consolidated_View'[Final_Count]))
return if(dr=0,blank(),'ABCR_Consolidated_View'[Delta]/dr*100)
but i'm getting the following error
Function 'SUM' is not allowed as part of calculated column DAX expressions on DirectQuery models
is there anyway to do it in direct query model.
Icey thanks for the solution
In my case I'm getting some error so i used the divide function to solve the problem.
Delta1 = DIVIDE ( SUM ( 'ABCR_Consolidated'[Delta]), SUM ('ABCR_Consolidated'[Final_Count] ) )
6 Replies
Hi Anonymous
try a measure, not calculated column
https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about
Quoting:
"Limitations in calculated columns: Calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions. Additionally, the DAX scalar functions, such as
LEFT(), that are allowed, are limited to those functions that can be pushed to the underlying source. The functions vary depending upon the exact capabilities of the source. Functions that aren't supported aren't listed in autocomplete when authoring the DAX for a calculated column, and would result in an error if used."
Columns are nor supported. Measures are. Refer my article how to handle these look for net sales , gross sales, and discount
Hi Anonymous ,
Try this to create a measure:
Percentag = VAR dr = CALCULATE ( SUM ( 'ABCR_Consolidated_View'[Final_Count] ) ) RETURN IF ( dr = 0, BLANK (), MAX ( 'ABCR_Consolidated_View'[Delta] ) / dr * 100 )Please tell me if it works.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Icey thanks for the solution
In my case I'm getting some error so i used the divide function to solve the problem.
Delta1 = DIVIDE ( SUM ( 'ABCR_Consolidated'[Delta]), SUM ('ABCR_Consolidated'[Final_Count] ) )Hi Anonymous ,
Glad to hear that. Please accept your reply as a solution so that people who may have the same question can get the solution directly.
Best Regards,
Icey
