Forum Discussion
Dynamic column concatenating distinct values
Hi,
I have an issue I am working on and have tried a couple different methods but wondering if there is a better way.
I have two tables, REVENUE and WAGES.
These tables are linked together with a key made up of a Route ID, year, and month.
There is a field in the WAGES table called "EN CODE" that can differ across the records for that particular key. So if it were key "ABCDEF|2019|8", there could be 30 records with 5 distinct values in the EN CODE column. There is also a field in that table called "Date Worked".
I am trying to create a field called "EN Code(s)" that lists all the distinct values concatenated together from the EN CODE field for the current filtered data. It needs to be dynamic depending on what the filters are that the user has chosen.
I have tried creating the field in SQL but of course then it is not dynamic. It lists everything for that key regardless of whether you add additional filters. I have also tried creating a separate query in the Query Editor, using Group By and Extract Values, but then the other fields that I would want to filter off of are no longer available in the table (Date Worked).
Am I missing another method?
Thanks in advance.
Hi heatherl ,
I would used CONCATENEX, and SELECTEDVALUE
Concatenates the result of an expression evaluated for each row in a table.
Syntax
DAXCopyCONCATENATEX(<table>, <expression>, [delimiter])Parameters
Term Definitiontable The table containing the rows for which the expression will be evaluated. expression The expression to be evaluated for each row of the table. delimiter (optional) A separator to use during concatenation. Return value
A text string.
SELECTEDVALUE
- 12/09/2018
- 2 minutes to read
Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.
Syntax
DAXCopySELECTEDVALUE(<columnName>[, <alternateResult>])
Parameters
Term DefinitioncolumnName The name of an existing column, using standard DAX syntax. It cannot be an expression. alternateResult (Optional) The value returned when the context for columnName has been filtered down to zero or more than one distinct value. When not provided, the default value is BLANK(). Return value
The value when the context for columnName has been filtered down to one distinct value only. Else, alternateResult.
Hi Nathaniel_C , v-lili6-msft ,
Thanks to both of you for your help. I discovered that if I use my same code creating a measure instead of a custom column, then it works as desired.
EN Code(s) new = calculate(CONCATENATEX(values(WAGES[EN Code]),WAGES[EN Code],","))
So thank you for that!
I have another resulting issue but I will open a separate discussion for it. Thanks again!
5 Replies
Hi heatherl ,
I would used CONCATENEX, and SELECTEDVALUE
Concatenates the result of an expression evaluated for each row in a table.
Syntax
DAXCopyCONCATENATEX(<table>, <expression>, [delimiter])Parameters
Term Definitiontable The table containing the rows for which the expression will be evaluated. expression The expression to be evaluated for each row of the table. delimiter (optional) A separator to use during concatenation. Return value
A text string.
SELECTEDVALUE
- 12/09/2018
- 2 minutes to read
Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.
Syntax
DAXCopySELECTEDVALUE(<columnName>[, <alternateResult>])
Parameters
Term DefinitioncolumnName The name of an existing column, using standard DAX syntax. It cannot be an expression. alternateResult (Optional) The value returned when the context for columnName has been filtered down to zero or more than one distinct value. When not provided, the default value is BLANK(). Return value
The value when the context for columnName has been filtered down to one distinct value only. Else, alternateResult.
Hi Nathaniel_C ,
Thanks for your assistance. Can you explain what the SELECTEDVALUE would be doing here? I'm not understanding what exactly what it does.
Based on your CONCATENATEX suggestion I did some more searching and put together this column.
test = calculate(CONCATENATEX(values(WAGES[EN Code]),WAGES[EN Code],","))
It works at getting the distinct values but it is working the same as my SQL column; when the wages filters are updated, the field still show all of the values even if they are no longer relevant.
Thanks
Hi heatherl ,
Your test seems to work well with folders. Here is my pbix with a simple data table, file If what you are trying to do is to create a column in a table based on the users' filters, I have never seen that. However if you are doing it for a dashboard this seems to do the job. Let us know if there is more intricacies that need to be dealt with.