Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Can we convert Calculated Column to Measure ?

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
 
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 

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

5 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous 
    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
    )

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Hi Anonymous 
      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
            )
    )
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.