Concatenate Columns If Same ID

[khappersett]

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 concatenate all the years for each item. For example...

Item ID          Catalog Name         Catalog Year

A                          Spring                   2015

A                          Spring                   2016

A                          Spring                   2017

B                          Fall                        2015

B                          Fall                        2016

B                          Fall                        2017

And I want a new measure that would add this to the table.....

Item ID          Catalog Name         Catalog Year         All Catalog Years

A                          Spring                   2015                 2015, 2016, 2017

A                          Spring                   2016                 2015, 2016, 2017

A                          Spring                   2017                 2015, 2016, 2017

B                          Fall                        2014                 2014, 2016, 2017

B                          Fall                        2016                 2014, 2016, 2017

B                          Fall                        2017                 2014, 2016, 2017

This is my current DAX code, but when I use it in a visual I am getting the message "Can't display the visual"

All Years = CONCATENATEX('Catalog Dict', 'Catalog Dict'[yr], VALUES('Catalog Dict'[item_id]), ", ")

I have also tried the following, but it did not give me the correct results.

All Years = CONCATENATEX('Catalog Dict', 'Catalog Dict'[yr], ", ")

Ultimately, I am trying to find a way to count the items that are in a catalog for one year, but not another. So count items that were in a 2017 catalog, but not in a 2016 catalog. Couldn't think of an easy way to do that, so thought I could make a new measure (or perhaps a calculated column) that would return all catalog years for each item and then do a DISTINCTCOUNT with a filter checking if the new calculated column contains "2017" but not "2016, for example. Any help is appreciated.

[P3Tom · Accepted solution]

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

[v-shex-msft · Accepted solution]

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

[ewkcoder]

This worked for my use case.  I extended this with an additional condition to exclude a field value from being included in the concatenation.

All Year = CONCATENATEX(FILTER(ALL('sample'),[Item ID]=EARLIER('sample'[Item ID]) && [Item ID]<>"Not Supplied"),[Catalog Year],",")

[filipe197]

Hello @v-shex-msft 

What if additionally you have for the same Item ID duplicate Catalog Year? How could you consider in your formula only the unique values? I mean, if item ID "A" have in column "Catalog Year" the values 2012,2012 and 2014 i just want to consider in "All year" this result: 2012,2014. 

Thanks,

filipe197

[Ashish_Mathur]

Hi @filipe197,

Do you want that as a calculated column formula or as a measure?

[filipe197]

Hi @Ashish_Mathur

As a column.

[Ashish_Mathur]

Hi,

I have done this using Power Query and then pushed the data into the Data Model.  Will this be OK with you?

[filipe197]

Hello @Ashish_Mathur

I can see on your sample that item B has in catalogue year column the value of 2014 twice and in column All Year you have made the concatenation without repeating 2014. That's what I need. What's the formula you have set for this?

[Ashish_Mathur]

Hi @filipe197,

You may refer to my solution here.

Hope this helps.

[filipe197]

Hi @Ashish_Mathur

I dont use power query and even if i want to, i can't install it in my pc 😞 can you just tell me the dax formula you've written to get de outcome of "All Year" column?

Many thanks,

filipe197

[Ashish_Mathur]

Hi,

Power Query comes with the Power BI desktop (Home > Edit Queries.  The Query Editor window opens up is Power Query).  Download the PowerBI desktop solution from here.

Hope this helps.

[P3Tom]

Although this topic is titled, "Concatenate Columns If Same ID", later in the original request it says:

"Ultimately, I am trying to find a way to count the items that are in a catalog for one year, but not another."

Restated, concatenation was a means used to count the items that are in a catalog only for one year, but not another.

A complete solution will also give an answer to the ultimate end (what is the count of catalog items that were listed for only one year).

Tom

[P3Tom · Accepted solution]

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

[lsuresh89]

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!

[Anonymous]

@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:

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

Say I added in the following years

1. 2016
2. 2016
3. 2014
4. 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]

Hi,

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

=COUNTROWS(Data)

Hope this helps.

[Anonymous]

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

Hi,

I will not be able to help you with this.  Someone else will.  Sorry.