topic Re: Concatenate distinct values from column in Measure in DAX Commands and Tips

Concatenate distinct values from column in Measure

Anonymous — Thu, 17 Dec 2020 12:29:16 GMT

Hi,

I've a table (UserPermission) with 3 columns (USERID, REGION, DIVISION). i need to filter out the data for a particular userid connected to the server and then create a measure for Region and division. Below is how the data looks like

UserID	Region	Division
corp\abcded	Americas	NULL
corp\abcded	Americas	Globals
corp\abcded	Americas	NULL
corp\abcded	Americas	Americas

I've tried using the below query, but the issue here is it doesn't return me the disticnt values.

RegionMeasure:=

CONCATENATEX(FILTER(RELATEDTABLE(UserPermission),UserPermission[UserId]=USERNAME()),UserPermission[Region],",")

output expected - Americas

DivisionMeasure:=

CONCATENATEX(FILTER(RELATEDTABLE(UserPermission),UserPermission[UserId]=USERNAME()),UserPermission[Division],",")

output expected - Globals,Americas (order doestn't matter)

Re: Concatenate distinct values from column in Measure

amitchandak — Thu, 17 Dec 2020 13:20:42 GMT

Anonymous , why you need related table, is not clear to me , Try like

CONCATENATEX(FILTER(UserPermission,UserPermission[UserId]=USERNAME()),UserPermission[Division],",")

Re: Concatenate distinct values from column in Measure

Anonymous — Thu, 17 Dec 2020 13:42:06 GMT

Hi Amit,

It doesnt give me the distinct list, it concatenates all the values and the output will be like - "Americas, Americas, Americas, Americas".