Calculate consumption from 5 minutes meter reads

[Anonymous]

Hi,

I have the exact same request discribed in the link below but with a very large database. I tested the solution in the link below on import mode with a fraction of my database and it works fine.

link to the request

When I test the same code on DirectQuery mode, a message pops up :

" this step generates a query not supported on DirectQuery mode"

I think DirectQuery mode doesn't support adding columns.

Is there another way to do the same thing on DirectQuery or using the new feature mixte storage ?

thanks for your help.

Best regards

[Greg_Deckler]

That link isn't working for me, but make sure that your fuctions are compatible with Direct Query

https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-d...

Might work with composite mode but maybe not if you are using a non-compatible function.

[Anonymous]

Thanls for your answer and sorry for the link,

this link should work

the M code that wont work in Direct Query mode :

    Partition = Table.Group(#"Lignes filtrées" , {"tagName"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"ts", Order.Ascending}}), "Index",1,1), type table}}),
    Table.Combine = Table.Combine(Partition[Partition]),
    #"Added Conditional Column" = Table.AddColumn(Table.Combine, "Index-1", each if [Index] = 1 then null else [Index]-1),
    #"Merged Queries" = Table.NestedJoin( #"Added Conditional Column",{"tagName", "Index-1"},#"Added Conditional Column",{"tagName", "Index"},"Renamed Columns",JoinKind.LeftOuter),
    #"Expanded Renamed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns", {"tagValue"}, {"tagValue.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Renamed Columns", "conso", each if [tagValue] < [tagValue.1] or [tagValue.1] = null  then null else [tagValue]-[tagValue.1]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ts", "tagName", "tagValue", "conso"})

It seems that the problem is adding columns.