Dynamic column concatenating distinct values

[heatherl]

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.

[Nathaniel_C · Accepted solution]

Hi @heatherl ,

I would used CONCATENEX, and SELECTEDVALUE

Concatenates the result of an expression evaluated for each row in a table.

Syntax

DAXCopy

CONCATENATEX(<table>, <expression>, [delimiter])  

Parameters

Term Definition

table	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

DAXCopy

SELECTEDVALUE(<columnName>[, <alternateResult>])  

Parameters

Term Definition

columnName	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.

[Nathaniel_C · Accepted solution]

Hi @heatherl ,

I would used CONCATENEX, and SELECTEDVALUE

Concatenates the result of an expression evaluated for each row in a table.

Syntax

DAXCopy

CONCATENATEX(<table>, <expression>, [delimiter])  

Parameters

Term Definition

table	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

DAXCopy

SELECTEDVALUE(<columnName>[, <alternateResult>])  

Parameters

Term Definition

columnName	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.

[heatherl]

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

[v-lili6-msft]

hi, @heatherl 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

For your case, I think you need to add the filter context in the formula.

test = calculate(CONCATENATEX(values(WAGES[EN Code]),WAGES[EN Code],",") ,<filter1>)

Best Regards,

Lin

[heatherl · Accepted solution]

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!

[Nathaniel_C]

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.