Calculated Column Measure Error

[Anonymous]

Hi Experts

Getting the following error message when using calculate column measre with DirectQuery.....is there a work around

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

Try like following ,if not work ,could you pls share your pbix file?Remember to remove confidential data.

 MDX:

AOV.=
CALCULATE (
    SUM ( factsalesordertable[Gross_Order_value] ),
    FILTER (
        ALL ( 'factsalesordertable' ),
        'factsalesordertable'[Gross_Order_Value]
            = MAX ( 'factsalesordertable'[Gross_Order_Value] )
    )
)

If use sql ,adjust accord the following SQL:(Remember to backup base data table)

Step 1,

Create table table1 as  select T.*,SUM(T.Gross_Order_Value)  from   FACTSalesOrdertable T  group by t.*.

 Step 2，

drop table1 purge.

Step 3,

alter table table1 rename table.

Wish it is helpful for you!

Best Regards

Lucien

[Pragati11]

HI @Anonymous ,

There are certain limitations of using Directquery mode in Power BI. One of them is creating calculated columns.

If you want to create a column do it in the source not in Power BI as this is a limitation with DirectQuery mode or you can see if a measure will work for your scenario.

For details check here:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

Thanks,

Pragati

[Anonymous]

Hi - How would i write this as SQL and or MDX.....totally stuck. Please