New column based on distinct value

[Nun]

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!

[Greg_Deckler]

@Nun - Not sure I completely understand, but maybe:

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

[amitchandak]

@Nun , Try a new column like

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

[Nun]

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]

@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 

[Nun]

@Fowmy 

thanks for supporting, unfortunately I got the same results as earlier:

Col3 with ID+names instead to get a Col3 with names and only in case of two or more IDs for the same name, the output is ID+name

thanks!

[Fowmy]

@Nun 

As per your initial question, this formula shows the desired results? 

________________________

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

[Nun]

@Fowmy 

I tried your solution, as result the col3 is

11

22

33

44

..

it seems the output is a duplicate of the IDs.

[Fowmy]

@Nun 

Can you show me the Measure you used and the screenshot of your table?

________________________

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

[Nun]

@Fowmy 

I did a correction, now the result is all the names with the IDs. Please find enclosed the information you required. The results (Column) should print the ID only if the same name( in the previous example there is Abc twice) has more than one ID (same name but ID is 1 and 2).

Thanks!

[Nun]

@Fowmy 

an additional information that can explain the reason that your solution doesn't work with my table.

Col1 has several rows with the same name (Abc) and several rows with the same IDs

Col1     Col2

Abc       1

Abc       2

Test      3

Test       3

Test1    4

Test1    4

Test2     10

with the meausure you proposed only Test2 is printed out with only the name.

Thanks

[Greg_Deckler]

@Nun - So what is the expected output of that last table of information that you posted? It helps to be thorough when posting sample data.

[Nun]

@Greg_Deckler ,

thanks for asking.

The output should be a new col. where the ID is printend out before the name only if there are several IDs for the same name. In this table:

Col1     Col2

Abc       1

Abc       2

Test      3

Test       3

Test1    4

Test1    4

Test2     10

the new col is:

Col3

1Abc

2Abc

Test

Test

Test1

Test1

Test2

Abc name is the olny value with two IDs, for that reason the output is with ID before the name.

Thank you!

[Greg_Deckler · Accepted solution]

@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])

[Nun]

@Greg_Deckler 

thanks a lot!!! it works just as I wished...thank you so so much!

[Greg_Deckler]

@Nun Sample data, expected output, works every time! 🙂