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 ta...
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.

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