cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cyclistpro131 Regular Visitor
Regular Visitor

Measure that returns list of concatenated selection based on another column

Hi All,

 

I've been having some trouble figuring out a proper DAX statement that would return the user selection however based on another column..

 

So for example I have the following table:

Selection OptionsOther Values
A10
B20
C30
D40
E

50

 

The slicer would look at the "Selection Options" column as a mult select.. The user selects "B", "D", and "E" for example..

I want the measure to return something like "Other Values: 20, 40, 50" as the corresponding values to the user selection..

 

I've figured out using ALLSELECTED and CONCATENAX how to create a concatenated value of the user selection.. however not returning it based on another field.. I suspect it will involved using CALCULATE and FILTER.. however no such luck so far..

 

Any suggestions are appreciated! 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Measure that returns list of concatenated selection based on another column

I created an OtherValues as you described and an InverseOtherValues just in case you want that.

 

OtherValues = 
VAR __currentTable = ALLSELECTED('Table3')
RETURN
CONCATENATEX(__currentTable,[Other Values],",")
InverseOtherValues = 
VAR __allTable = ALL('Table3')
VAR __currentTable = ALLSELECTED('Table3')
VAR __table = EXCEPT(__allTable,__currentTable)
RETURN
CONCATENATEX(__table,[Other Values],",")

See page 2, Table 3 of attached.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


4 REPLIES 4
Super User
Super User

Re: Measure that returns list of concatenated selection based on another column

I created an OtherValues as you described and an InverseOtherValues just in case you want that.

 

OtherValues = 
VAR __currentTable = ALLSELECTED('Table3')
RETURN
CONCATENATEX(__currentTable,[Other Values],",")
InverseOtherValues = 
VAR __allTable = ALL('Table3')
VAR __currentTable = ALLSELECTED('Table3')
VAR __table = EXCEPT(__allTable,__currentTable)
RETURN
CONCATENATEX(__table,[Other Values],",")

See page 2, Table 3 of attached.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


cyclistpro131 Regular Visitor
Regular Visitor

Re: Measure that returns list of concatenated selection based on another column

Thanks so much Greg!

 

What a neat solution!

cyclistpro131 Regular Visitor
Regular Visitor

Re: Measure that returns list of concatenated selection based on another column

If you are feeling especially generous would you know how to tweak your code to return the distinct values only in the concatenation? 

 

My table example has repeating values in the [Other Values] column.. 

Essentially like this example, however with my caveat of being based on another column being filtered in the selection.. 

 

Any insight is appreciated Smiley Happy 

cyclistpro131 Regular Visitor
Regular Visitor

Re: Measure that returns list of concatenated selection based on another column

I was able to figure this out.. however I'm not sure why this works..

 

OtherValuesList =
CALCULATE(CONCATENATEX(VALUES(Sheet1[OtherValues]), Sheet1[OtherValues], ", ")
)
 
This does return a distinct list of values from the OtherValues column.. even though the slicer is looking at a different column (Selection Options).. It seems odd that it can intuitively filter in this contex.. However otherwise you need to establish the current selection as its own variable table as Greg_Deckler provided..