Measure that returns list of concatenated selection based on another column

[Anonymous]

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 Options	Other Values
A	10
B	20
C	30
D	40
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! 

[Greg_Deckler · Accepted solution]

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.

[Anonymous]

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 🙂 

[Anonymous]

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

[Anonymous]

Thanks so much Greg!

What a neat solution!