DAX code to select 3 minimum values across matrix row, then return a concatenation of said values

[iluvcoding_91]

Hello, thank you all for taking the time to reply to my post. I am looking for help in writing a DAX code which iterates through a matrix row and selects the top three minimum values, then returns a concatenation of the three minimum values in text for. For example, the table below returns the three minimum values for each row of the matrix:

I've tried setting up a DAX code using a combination of variables, temp tables, MINX, and concatentation, but the concatentation will only pick up the first piece of text and will leave out the remaining piece of text. Any help would be greatly appreciated!!

Test =

VAR COL = [COL]

VAR HOUSING = [HOUSING]

VAR GASOLINE = [GAS]

VAR FOOD = [FOOD]

VAR VEHICLE = [VEHICLE]

VAR HEALTHCARE = [HEALTHCARE

VAR Temp_Tbl = {COL, HOUSING, GASOLINE, FOOD, VEHICLE, HEALTHCARE}

VAR Min_Value_1 =

MINX( Temp_Tbl, [Value])

VAR Min_Column_1 =

{

SWITCH(

Min_Value_1,

COL, "COL",

HOUSING, "HOUSING",

GASOLINE, "GASOLINE",

FOOD, "FOOD",

VEHICLE, "VEHICLE",

HEALTHCARE, "HEALTHCARE"

)

}

VAR Min_Value_2 =

MINX( Temp_Tbl, [Value])+1

VAR Min_Column_2 =

{

SWITCH(

Min_Value_2,

COL, "COL",

HOUSING, "HOUSING",

GASOLINE, "GASOLINE",

FOOD, "FOOD",

VEHICLE, "VEHICLE",

HEALTHCARE, "HEALTHCARE"

)

}

Return

Min_Column_1&", "&Min_Column_2

[johnt75]

I think it should work if you wrap each column reference inside SELECTEDVALUE

[iluvcoding_91]

How would I set that up?

[johnt75 · Accepted solution]

Create a new measure as

Minimum Three =
var tmpTable = { ( "Cost of living", SELECTEDVALUE('Table'[Cost of living])), 
( "Housing", SELECTEDVALUE('Table'[Housing]))
... etc
}
return CONCATENATEX( TOPN( 3, tmpTable, [Value2], ASC), [Value1], ", ")

[iluvcoding_91]

Thanks, but that didn't work b/c the columns in the matrix are measure and not actual columns in the data model. Is there another function I can use instead of selectedvalues?

[johnt75]

Just use [Cost of living] instead of the column reference. The measures should each be calculated in the correct context from the matrix visual, so it should work

[iluvcoding_91]

Thank you so much!!! That worked!!

[johnt75]

You could try adding a calculated column as

Minimum Three =
var tmpTable = { ( "Cost of living", 'Table'[Cost of living]), 
( "Housing", 'Table'[Housing])
... etc
}
return CONCATENATEX( TOPN( 3, tmpTable, [Value2], ASC), [Value1], ", ")

[iluvcoding_91]

Hi, thank you for your reply. Is there a way to set this up w/o using calculated columns and using a measure only? I have a live connection to the datasource so unfortunately I can't created calculated columns...