March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to 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
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
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
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],",")
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
Hi @filipe197,
Do you want that as a calculated column formula or as a measure?
Hi,
I have done this using Power Query and then pushed the data into the Data Model. Will this be OK with you?
Hi @filipe197,
You may refer to my solution here.
Hope this helps.
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
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.
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
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
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!
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:
Say I added in the following years
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
Hi,
Just drag the Year column to the Row labels and then write this measure
=COUNTROWS(Data)
Hope this helps.
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 😊
Hi,
I will not be able to help you with this. Someone else will. Sorry.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
76 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |