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.

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.