Forum Discussion

markharoldr's avatar
markharoldr
Regular Visitor
3 years ago

Creating Calculated Table With some filtering based on row values

Hello Everyone, 

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

The aim is to only include rows with items from VersionItem, 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

 

4 Replies

  • DoubleJ's avatar
    DoubleJ
    Solution Supplier

    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's avatar
      markharoldr
      Regular Visitor

      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's avatar
        DoubleJ
        Solution Supplier

        how long does it take  to compute the calculated column?