Can we convert Calculated Column to Measure ?

[miiihiir]

Hi all,
I am new to DAX, and wanted to convert this calculated column to measure, because it is taking too much time for running the query as a calculated column.

Energy_consumption = 

VAR Dates = FloatTable[DateAndTime]

VAR Index = FloatTable[TagIndex]

VAR Prev_Row = CALCULATE(MAX(FloatTable[Val]), FILTER(FloatTable, FloatTable[DateAndTime] < Dates && FloatTable[TagIndex] = Index ))

RETURN

IF(FloatTable[TagTable.Parameter - Copy] = "ENERGY CONSUMTION", FloatTable[Val]-Prev_Row, 0)

Logic: So basically my data have different Tag Indexes and I need to do groupby on it, and there is a column "Val" which has Cummulative values for energy consumtion, and I need to get instantaneous value rather than cummulative. So this formula is subtracting the value from previous record based on time. 
This formula is giving me correct value, but when I load full data from DB then I am not able to run this formula, it is showing Memory Error.

Please can anyone help me in Optimizing this formula or converting it to measure.

Thanks & Regards
Mihir
 

[miiihiir]

Thankyou @tamerj1  it is working fine with small dataset... Yeah for measure, filter context would be TagIndex.

Btw when I am executing this formula in Full Dataset then it is showing again Memory Error: 

Can we optimize it more if possible or should we shift this process to Power Query level?

[tamerj1]

Hi @miiihiir 
Please try this measure

Energy_consumption =
VAR CurrentIndexTable = FloatTable
RETURN
    SUMX (
        CurrentIndexTable,
        VAR CurrentDate = FloatTable[DateAndTime]
        VAR FilteredTable =
            FILTER ( CurrentIndexTable, FloatTable[DateAndTime] < CurrentDate )
        VAR PreviousRecord =
            TOPN ( 1, FilteredTable, FloatTable[DateAndTime] )
        VAR ReviousValue =
            MAXX ( PreviousRecord, FloatTable[Val] )
        VAR CurrentValue = FloatTable[Val]
        RETURN
            IF (
                FloatTable[TagTable.Parameter - Copy] = "ENERGY CONSUMTION",
                CurrentValue - ReviousValue,
                0
            )
    )

[miiihiir]

Hey @tamerj1 

I think there is  some small issue in this measure, it is still giving same Value instead of previous value, for now I have only taken the Prev_row and skipped that subtracting part.

[tamerj1 · Accepted solution]

Hi @miiihiir 

please try

Energy_consumption =
VAR CurrentIndexTable =
    CALCULATETABLE ( FloatTable, ALLEXCEPT ( FloatTable, FloatTable[TagIndex] ) )
RETURN
    SUMX (
        CurrentIndexTable,
        VAR CurrentDate = FloatTable[DateAndTime]
        VAR FilteredTable =
            FILTER ( CurrentIndexTable, FloatTable[DateAndTime] < CurrentDate )
        VAR PreviousRecord =
            TOPN ( 1, FilteredTable, FloatTable[DateAndTime] )
        VAR ReviousValue =
            MAXX ( PreviousRecord, FloatTable[Val] )
        VAR CurrentValue = FloatTable[Val]
        RETURN
            IF (
                FloatTable[TagTable.Parameter - Copy] = "ENERGY CONSUMTION",
                CurrentValue - ReviousValue,
                0
            )
    )

[tamerj1]

Hi @miiihiir 
Please try thr following as a caclulated column. For a measure please advise what is the filter context of your visual, for example are you presenting this value by [TagIndex]?

Energy_consumption =
VAR CurrentDate = FloatTable[DateAndTime]
VAR Index = FloatTable[TagIndex]
VAR CurrentIndexTable =
    CALCULATETABLE ( FloatTable, ALLEXCEPT ( FloatTable, FloatTable[TagIndex] ) )
VAR FilteredTable =
    FILTER ( CurrentIndexTable, FloatTable[DateAndTime] < CurrentDate )
VAR PreviousRecord =
    TOPN ( 1, FilteredTable, FloatTable[DateAndTime] )
VAR ReviousValue =
    MAXX ( PreviousRecord, FloatTable[Val] )
VAR CurrentValue = FloatTable[Val]
RETURN
    IF (
        FloatTable[TagTable.Parameter - Copy] = "ENERGY CONSUMTION",
        CurrentValue - ReviousValue,
        0
    )