cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heatherl Frequent Visitor
Frequent Visitor

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

Re: Dynamic column concatenating distinct values

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
tableThe table containing the rows for which the expression will be evaluated.
expressionThe 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
columnNameThe 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.

 

View solution in original post

heatherl Frequent Visitor
Frequent Visitor

Re: Dynamic column concatenating distinct values

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!

 

 

View solution in original post

5 REPLIES 5
Nathaniel_C Super Contributor
Super Contributor

Re: Dynamic column concatenating distinct values

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
tableThe table containing the rows for which the expression will be evaluated.
expressionThe 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
columnNameThe 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.

 

View solution in original post

Highlighted
heatherl Frequent Visitor
Frequent Visitor

Re: Dynamic column concatenating distinct values

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

Nathaniel_C Super Contributor
Super Contributor

Re: Dynamic column concatenating distinct values

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.

 

encode.PNG

Community Support Team
Community Support Team

Re: Dynamic column concatenating distinct values

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
heatherl Frequent Visitor
Frequent Visitor

Re: Dynamic column concatenating distinct values

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!

 

 

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 163 members 2,038 guests
Please welcome our newest community members: