Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 =
Solved! Go to 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
)
)
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?
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
)
)
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.
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
)
)
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
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |