Forum Discussion

pawelj795's avatar
pawelj795
Post Prodigy
5 years ago
Solved

Mark rows with the latest value

Hi,
I have a data table like below.

 


Now, I want to mark rows, which meet these conditions:

  • They contains duplicate values in both columns at the same time: parent_product_tmpl_id, product_tmpl_id
    (in my screenshot it will be ID: 290256, 153653)
  • Next, for rows, which meet the above condition, select the row with the latest date in a column create_date
    (in my screenshot it will be ID: 290256)
  • Furthermore, these marks must be created in a column, not a measure


Sample file:

https://drive.google.com/file/d/1JRVwaAN-WDW0xzo-WEAu9vUZM7yygyCW/view?usp=sharing

 

If something is not clear for you, please ask and I kindly respond.

Thanks in advance!

Need Help
Show and Tell
Tips and Tricks

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    pawelj795 , You can create a new column like

     

    New column =
    var _max = maxx(filter(Table, [parent_product_tmpl_id] = earlier([parent_product_tmpl_id]) && [product_tmpl_id] = earlier([product_tmpl_id])),[create_date])
    return
    if([create_date] =_max , 1, 0)

     

     

    • pawelj795's avatar
      pawelj795
      Post Prodigy
      5 years ago

      amitchandak 
      Thanks for your response.
      Unfortunately, your column doesn't work.


      My main goal is to filter out rows, which are duplicates with the earliest values, however I want to keep the other rows, which don't meet the first condition

       

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        pawelj795 , try Id in place of date

         

        New column =
        var _max = maxx(filter(Table, [parent_product_tmpl_id] = earlier([parent_product_tmpl_id]) && [product_tmpl_id] = earlier([product_tmpl_id])),[ID])
        return
        if([ID] =_max , 1, 0)

    • pawelj795's avatar
      pawelj795
      Post Prodigy
      5 years ago

      ryan_mayu 
      amitchandak 

      Sorry, but both of your answers are not working properly.

      My goal is to filter out rows that meet all of these conditions.
      In your answers, it works fine for duplicates but simultaneously it filters out also some rows with distinct values.
      This column must work for the whole data table.

      Moreover, the answer must be created in one column, not divided by 2 columns.

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    Hi pawelj795 ,

     

    Please refer to my .pbix file.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.