Forum Discussion

PeterTomažin's avatar
PeterTomažin
Regular Visitor
5 years ago
Solved

Selecting only secound value from duplicate pairs

I am trying to select only rows of secound values in duplcate pairs from my table. The problem is that I cannot seem to find the right Filter/Function to do this in DAX.

Below is a sample from my table, from which I removed sesitive data.

Decode colums of the table:

  1. ID - uniqe ID
  2. Date - date of insertion of data, this is where I want to Ignore first duplicate row and select only the secound one
  3. Tedenski izpis - date that is used for visualisation, Saturday(no sum across data)
  4. Duplicates - if set to 1, there are duplicates in the Datum vnosa cell(all of the duplicates are *always* inserted one after another as can be seen in the picture

An exaple of what I want to do in DAX:

  1. Select date first row
  2. Check for duplicates (IF([DuplicatiOverhead]=1))
    1. IF duplicate ignore first value (in this case, row with ID of 1)
      1. Write secound value to a separate column [ForGraph](in this case, a cell [Tedenski izpis] with ID of 2)
    2. IF no duplicates, write value of [Tedenski izpis] to separate column [ForGraph]
  3. Loop back until done

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi PeterTomažin 

     

    You can use below code to create a column. This only marks the first duplicated row as 1. [Date] is a column which only contains date values from the [Datum vnosa] column.

    Duplicated Row = 
VAR __latestID = MAXX(FILTER(SampleTable,SampleTable[Date]=EARLIER(SampleTable[Date])),SampleTable[ID])
RETURN
IF(SampleTable[ID]=__latestID,BLANK(),1)

     

    Then you can use this column to filter table in other DAX formulas. For example,

    Overhead Material Measure = CALCULATE(SUM(SampleTable[Overhead Material]),SampleTable[Duplicated Row]<>1)

     

    You can also remove the duplicated rows from the query in Power Query Editor if you don't want to use these data in the report further. Here is a similar thread for your reference: Select newest data in column 

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

5 Replies

  • PeterTomažin's avatar
    PeterTomažin
    Regular Visitor
    5 years ago

    If it helps, this is how I get [Tedeski zapis]:

         Tedenski izpis = IF([St dan]<>7, [Datum vnosa]-[St dan], [Datum vnosa])

    And this is how I get [DuplikatiOverhead] (I have to use a seperate date column because of the date format):

       Skrit datum = DAY([Datum vnosa])&". "&MONTH([Datum vnosa])&". "&[Year]

       DuplikatiOverhead = IF(CALCULATE(COUNTROWS(Overhead'),FILTER('Overhead',[Skrit datum]=EARLIER([Skrit datum])))>1, "1", BLANK())

     
  • mussaenda's avatar
    mussaenda
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Please provide sample data where we  can copy. 

     

    Thank you

    • PeterTomažin's avatar
      PeterTomažin
      Regular Visitor
      5 years ago

      Here is a link to a zip archive with SampleData.xlsx and SampleDataDuplicates.pbix. https://ufile.io/hgprs86d

      I hope this helps you, sadly I can not give you the full pbix file for data security reasons. I have also added a sample graph from which you can see the type of graph that I get.

      As stated before, I get this columns by:

      • Tedenski izpis = IF([St dan]<>7, [Datum vnosa]-[St dan], [Datum vnosa])
      • Skrit datum = DAY([Datum vnosa])&". "&MONTH([Datum vnosa])&". "&[Year]
      • DuplikatiOverhead = IF(CALCULATE(COUNTROWS(Overhead'),FILTER('Overhead',[Skrit datum]=EARLIER([Skrit datum])))>1, "1", BLANK())

      Thanks for help mussaenda.

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi PeterTomažin 

     

    You can use below code to create a column. This only marks the first duplicated row as 1. [Date] is a column which only contains date values from the [Datum vnosa] column.

    Duplicated Row = 
VAR __latestID = MAXX(FILTER(SampleTable,SampleTable[Date]=EARLIER(SampleTable[Date])),SampleTable[ID])
RETURN
IF(SampleTable[ID]=__latestID,BLANK(),1)

     

    Then you can use this column to filter table in other DAX formulas. For example,

    Overhead Material Measure = CALCULATE(SUM(SampleTable[Overhead Material]),SampleTable[Duplicated Row]<>1)

     

    You can also remove the duplicated rows from the query in Power Query Editor if you don't want to use these data in the report further. Here is a similar thread for your reference: Select newest data in column 

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

    • PeterTomažin's avatar
      PeterTomažin
      Regular Visitor
      5 years ago

      Yeah, I've figured out that Power Querry -> Remove Duplicates was the easiest solution to this. Anyway, thanks for help v-jingzhang.