Creating Calculated Table With some filtering based on row values

[markharoldr]

Hello Everyone, 

I want to create a calculated table based from this existing table:

The aim is to only include rows with items from Version, Item, Change columns where there is a change in value under Change. 
Example Result:

Any clues on how to do this? Getting confused for the formula for DAX with this one. 

Regards,

Mark

[DoubleJ]

Hi

I did this in 3 steps:
1. In Power Query add an Index row (row is called ID)
2. Add a custom column "HasChanged" in DAX. Check if the value of the "Change" column has switched (compared to the row with closest lower ID). Output is "true" or "false"
3. Create a table using only the rows with "HasChanged" = "true"

Add Index column in PQ (step 1):

Formula for calculated column (step 2):

HasChanged = /*Calculated Column => every row in the table is iterated*/
VAR _ID = Versions[ID]              /*ID of current row*/
VAR _Change = Versions[Change]      /*Change of current row*/
VAR _PrevID =                       /*Get the ID of the previous row*/
MAXX(
    FILTER(
        Versions,
        Versions[ID] < _ID),
    Versions[ID])
VAR _ChangePrevID =                 /*Get Change of previous row*/
MINX(
    FILTER(
        Versions,
        Versions[ID] = _PrevID),
    Versions[Change])

RETURN _Change <> _ChangePrevID     /*Check if "Change" has a different value than in previous row*/  

Formula for calculated table (step 3):

Versions with changes = CALCULATETABLE(Versions,Versions[HasChanged])

I'm sure there are more elegant and compact ways to do this.  Hope this helps anyway.
JJ

[markharoldr]

Thanks @DoubleJ . It works as expected and I need to sort first the table by Item and Version in power query. Though in a large data set around 100k rows it seems it is taking a while to compute the calculated column.

Appreciated your answer 🙂

Regards,

Mark 

[DoubleJ]

how long does it take  to compute the calculated column?

[markharoldr]

Around 15 minutes for 100k rows