Calculated column for direct query

[Anonymous]

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]

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.

[Anonymous · Accepted solution]

@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] ) )

[Icey]

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

[amitchandak]

Columns are nor supported. Measures are. Refer my article how to handle these look for net sales , gross sales, and discount

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...

[az38]

Hi @Anonymous 

try a measure, not calculated column

[Anonymous]

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