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. The visual below might add more than I can explain. Basically, I want to locate duplicate serial numbers in my data and return a value into a column that shows the order for the duplicate.

 

Actual:

 

What is desired:

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

9 Replies

  • lbendlin's avatar
    lbendlin
    Super 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
      Super 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. 

  • mahoneypat's avatar
    mahoneypat
    Microsoft 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

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

    hi Ben_jamin 

    You could also create  a column by this formula:

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

    Result:

     

    Regards,

    Lin

    • Ben_jamin's avatar
      Ben_jamin
      Frequent Visitor
      6 years ago

      v-lili6-msft This one is seeming to work although I get a duplicatation in the string of service order on a few. Any ideas why?

       

       

       

      Installation Code = Serial number

      • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    A small variant of the solutions already presented.

    This function should work  for the more general case of ID n-uplicate, giving as many columns as there are the maximum repetitions of the IDs.

     

    let
    rog= (tab as table, grpCols as list, splitCol) => let        
         grp = Table.Group(tab, grpCols, {{"ord", each Table.Column(_, splitCol)},  {"ord2", each Table.Column(_, splitCol)}}),
         tabGrp = Table.ExpandListColumn(grp, "ord"),
         addTab = Table.AddColumn(tabGrp, "addOrd", each try List.Difference([ord2],{[ord]}) otherwise ""),
         transTab = Table.TransformColumns(addTab, {"addOrd", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
         spliTab = Table.SplitColumn(transTab, "addOrd", Splitter.SplitTextByDelimiter(","), {"ord.1", "ord.2"}, ""),
         cleanTab = Table.RemoveColumns(spliTab,{"ord2"})
   in
         cleanTab
in
    rog

     

     

    As an example of a call.

     

     

    RestOfGroup(data, {"sernum","name"},"ord")