Forum Discussion

Resolver I

9 years ago

Solved

Concatenate Columns If Same ID

I have a table with rows for item ID, catalog name, and catalog year. Some items are released in multiple catalogs so they have multiple rows. I am trying to create a new measure that will concatenat...

Need Help

P3Tom
9 years ago
Since your data set did not have any items that were in only 1 year, I created a similar set which you can find in this linked pbix file.

Multiple table model is required to get the results above.

Here are the measures:

Count If Item Sold in Only 1 Year =
COUNTROWS ( FILTER ( 'Items', [Item Years in Catalog] = 1 ) )
Item Catalog Years =
IF (
    NOT ( ISBLANK ( SELECTEDVALUE ( Items[Item ID] ) ) ),
    CONCATENATEX ( RELATEDTABLE ( Data ), Data[Catalog Year], ", " )
)
Item Total Years in Catalog =
CALCULATE (
    COUNTROWS ( VALUES ( 'Years'[Year] ) ),
    CROSSFILTER ( Data[Catalog Year], Years[Year], BOTH )
)

Tom
www.powerpivotpro.com
Anonymous
9 years ago
Hi khappersett,

I think you need to filter the records which has the same id, then use this as the source of concatenate function.

All Year = CONCATENATEX(FILTER(ALL('sample'),[Item ID]=EARLIER('sample'[Item ID])),[Catalog Year],",")

Regards,

Xiaoxin Sheng

Anonymous

Not applicable

6 years ago

P3Tom

I concatenated values by ID number and now I am trying to figure out how to get a distinct count of similar concatenated fields.

For example, this is the following outcome:

2016
2014
2015, 2016
2015, 2016, 2017

Say I added in the following years

2016
2016
2014
2015, 2016

I want to be able to get a count of the unique concatenated fields:

2016	3
2014	2
2015, 2016	2
2015, 2016, 2017	1

Any help would be appreciated!

Thanks

Ashish_Mathur

Super User

6 years ago

Hi,

Just drag the Year column to the Row labels and then write this measure

=COUNTROWS(Data)

Hope this helps.

Anonymous
Not applicable
6 years ago
Ashish_Mathur ,

To better understand what I’m doing, below are two tables.

Using this formula, I was able to concatenate the different paths the residents took:

Continuum = IF ( NOT ( ISBLANK ( SELECTEDVALUE ( Episode[ResidentSys] ) ) ), CONCATENATEX( RELATEDTABLE( 'Unit Stay Table' ), 'Unit Stay Table'[Unit], ", " ) )

The table output that I got looks similar to this (I am using the matrix visualization):
Resident Number
Paths
1
L
2
L, L, A, A
3
R, L, A
4
A, I
5
A, I
6
I
7
I
8
I
9
A
10
A
11
L, R
12
L, R

I want to be able to do a “count” of the different unique paths to get an output like this:
Path
Number of Residents
L
1
L, L, A, A
1
R, L, A
1
A, I
2
I
3
A
2
L, R
2

Thank you so much for all your help!! I really appreciate it 😊
- Ashish_Mathur
  Super User
  6 years ago
  Hi,
  
  I will not be able to help you with this. Someone else will. Sorry.

Forum Discussion

Concatenate Columns If Same ID

Related content

Concatenate 3 columns

create custom column concatenated

Power query - concatenate text columns

Concatenate lists ;

Concatenate multiple columns to a single column

Resident Number	Paths
1	L
2	L, L, A, A
3	R, L, A
4	A, I
5	A, I
6	I
7	I
8	I
9	A
10	A
11	L, R
12	L, R

Path	Number of Residents
L	1
L, L, A, A	1
R, L, A	1
A, I	2
I	3
A	2
L, R	2