Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Concatenate distinct values from column in Measure

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 

 

UserIDRegionDivision
corp\abcded

Americas

NULL
corp\abcdedAmericasGlobals
corp\abcdedAmericasNULL
corp\abcdedAmericasAmericas

 

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)

2 Replies

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

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Amit,

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