Forum Discussion

pwrbiadm's avatar
pwrbiadm
Helper I
5 years ago
Solved

Removing cumulative frequency using power(m) query

Hi! I have the below table where the data gets generated every 1-2 seconds.   Column A - Datetime Column B - Cumulative Counter Column C - Calculated in Excel to remove cumulative frequency IF(B2...
Need Help
  • pwrbiadm's avatar
    pwrbiadm
    5 years ago

    I was able to use the below query for a dax calculated column which gave me the desired results without taking a long time to process the large dataset. Had to create an index column first in power query.

     

    Column = var e = CALCULATE(MAX(Query4[B]),FILTER(Query4,Query4[Index]=EARLIER(Query4[Index])-1))
    var f = Query4[B]
    return IF(f=0,0,IF(f>=e,f-e,e))
pwrbiadm's avatar
pwrbiadm
Helper I
5 years ago

I was able to use the below query for a dax calculated column which gave me the desired results without taking a long time to process the large dataset. Had to create an index column first in power query.

 

Column = var e = CALCULATE(MAX(Query4[B]),FILTER(Query4,Query4[Index]=EARLIER(Query4[Index])-1))
var f = Query4[B]
return IF(f=0,0,IF(f>=e,f-e,e))