Forum Discussion

pwrbiadm's avatar
pwrbiadm
Icon for Helper I rankHelper 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))
Icey's avatar
Icey
Icon for Community Support rankCommunity Support
5 years ago

Hi pwrbiadm ,

 

Due to your large dataset, it is suggested to create a measure like so:

Measure =
VAR ThisRow_B =
    MAX ( [B] )
VAR LastRow_B =
    CALCULATE (
        MAX ( 'Query1 (2)'[B] ),
        FILTER (
            ALLSELECTED ( 'Query1 (2)' ),
            'Query1 (2)'[Index]
                = MAX ( 'Query1 (2)'[Index] ) - 1
        )
    )
RETURN
    SWITCH ( TRUE (), ThisRow_B >= LastRow_B, ThisRow_B - LastRow_B, ThisRow_B )

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.