Forum Discussion

Ben_jamin's avatar
Ben_jamin
Frequent Visitor
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 Ben_jamin 

    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  Ben_jamin 

    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

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

hi Ben_jamin 

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

Ben_jamin's avatar
Ben_jamin
Frequent Visitor
6 years ago

hi v-lili6-msft ,

Thank you so much for the help. That seems to have worked. One last request...can a comman be added between the service order string? There are multiple service orders for some and it concatenates the orders into a group. 

 

Actual:

379172378740379174

Desired:

379172,378740,379174

 

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

    hi  Ben_jamin 

    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