Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Return a value from a duplicate

Hello Power BI community, I am seeking help regarding duplicates in my data. I'll take a shot at explaining it but I would like to find a way to display a value based on duplicate serial numbers. Th...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi Anonymous 

    If so, just adjust the formula as below:

    Order Number 2 =
    CONCATENATEX (
    FILTER (
    SUMMARIZE( 'Table','Table'[Order Number],'Table'[Serial number]),
    'Table'[Serial number] = EARLIER ( 'Table'[Serial number] )
    && 'Table'[Order Number] <> EARLIER ( 'Table'[Order Number] )
    ),
    'Table'[Order Number]
    )

     

     

    Regards,

    Lin

  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    Ok, try this:

    Order Number 2 =
    CONCATENATEX (
    FILTER (
    SUMMARIZE( 'Table','Table'[Order Number],'Table'[Serial number]),
    'Table'[Serial number] = EARLIER ( 'Table'[Serial number] )
    && 'Table'[Order Number] <> EARLIER ( 'Table'[Order Number] )
    ),
    'Table'[Order Number]
    ,
    ","
    )

     

     

    Regards,

    Lin

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

Will there only be duplicates? or can you also have triplicates etc?

 

In any case you can use a comparison together with TOPN.

 

Pseudo code:

 

Order Number 2 =

var ord = selectedvalue(table[Order Number])

var ser = selectedvalue(table[Serial Number])

var oth = TOPN(1,CALCUATETABLE(Table,[Serial Number]=ser,[Order Number]<>ord))

return CONCATENATEX(oth,[Order Number],blank())

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

Actual code:

 

Order Number 2 = 
var ord = selectedvalue(Actual[Order Number])
var ser = selectedvalue(Actual[Serial Number])
var oth = TOPN(1,CALCULATETABLE(Actual,Actual[Serial Number]=ser,Actual[Order Number]<>ord))
return CONCATENATEX(oth,[Order Number],blank())

 

You'll need to "show items with no data"  for the full table.