Forum Discussion

Nun's avatar
Nun
Icon for Resolver I rankResolver I
6 years ago
Solved

New column based on distinct value

Hello,

I have the following problem. I have a table with two columns, the first contains names, the second ID.

Col1 Col2
Abc    1
Abc    2  
Cde   1
Efg     6
Hxx   7
If the col1 has more than one distinct value (Abc has ID 1 and 2), join col2 + col1, else col1
Col3 should be
1Abc
2Abc
Cde
Efg
Hxx

Thanks!

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Nun - OK, not to steal Fowmy 's thunder here by maybe:

    Column =
  VAR __Table = SUMMARIZE(FILTER('Table',[Col1]=EARLIER([Col1])),[Col2])
RETURN
  IF(COUNTROWS(__Table)>1,[Col1]&[Col2],[Col1])

16 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Nun , Try a new column like

    if(countx(filter(Table, [Col1] =earlier([Col1]) ),[Col1])+0 >1,[Col1]&[Col2],[Col1])

    • Nun's avatar
      Nun
      Icon for Resolver I rankResolver I
      6 years ago

      Hello,

       

      thank you for the quick reply, unfortunately the output of Col3 is ID & Col1...no matter if name has an unique ID

      Col3

      1Abc

      2Abc

      1Cde

      6Efg

      7Hxx

      the ID should be added to the name only if it unique for the col1 (name)

      Thanks!

       

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        6 years ago

        Nun 

        Please try this code for a new column:

        Column = 
IF( COUNTROWS(FILTER(D,D[Col1]=EARLIER(D[Col1])))>1, D[Col2]&D[Col1],D[Col1])

        ________________________

        If my answer was helpful, please consider Accept it as the solution to help the other members find it

        Click on the Thumbs-Up icon if you like this reply 🙂

        YouTube  LinkedIn 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Nun - Not sure I completely understand, but maybe:

    Column =
  IF(DISTINCTCOUNT('Table'[Col2])>1,[Col1] & [Col2],[Col1])