Forum Discussion

iluvcoding_91's avatar
iluvcoding_91
Icon for Helper I rankHelper I
4 years ago
Solved

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

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's avatar
    johnt75
    4 years ago

    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], ", ")

8 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      iluvcoding_91
      Icon for Helper I rankHelper I
      4 years ago

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

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

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

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        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], ", ")