Selecting only secound value from duplicate pairs

[PeterTomažin]

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

[v-jingzhang · Accepted solution]

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]

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

[mussaenda]

Please provide sample data where we  can copy. 

Thank you

[PeterTomažin]

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.

[PeterTomažin]

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())