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

P3Tom

Helper I

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
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 😊
lsuresh89
Helper I
3 years ago
Hi P3Tom, This is an awesome solution. I just have an additional requirement which I'm unable to figure out. Could you help me with a solution to only show unique years in case I have a repitition of years. E.g If in the table above we have A+Spring+2015 twice, it should still give me only one instance while showing "Item catalog years" which in the example above would be 2015,2016,2017 and not 2015,2015,2016,2017.

Could you please help me with a way to come up with distinct values? Thanks!

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

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