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.

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.