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

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

You can use this DAX expression in a measure in a table visual with Order Number as one of the columns to get your desired result

 

Other Orders Same Serial Number =
VAR thisorder =
MIN ( Orders[Order Number] )
VAR sameserial =
CALCULATETABLE (
VALUES ( Orders[Order Number] ),
ALL ( Orders ),
VALUES ( Orders[Serial Number] ),
Orders[Order Number] <> thisorder
)
RETURN
CONCATENATEX ( sameserial, Orders[Order Number], "," )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat