Create measure to get min value of a selected dimension using another value of that dimension

[zausten]

Hi ,

I am trying to get the min value of one dimension based on selecting another value of that dimension through a common dimension. 

e.g. I have simple table with date, car, colour and amount.   The car can have multiple colours. Green is the cheapest.  I select red in a filter so I can display the cost of red in one visual, but I also want to see the cheapest for comparision.

My approach is get the car first of the selected colour, then get the list of all colours associated with that car, then get try to get the value of those colours however but this is just returning min value of the selected colour. 

Get_Cheapest_Colour =

var return_car = IF(ISFILTERED(Sheet1[Colour]),

values(Sheet1[Car]) ,BLANK() )

var get_all_colours = if( return_car <> BLANK(), VALUES(Sheet1[Colour]), BLANK())

var get_min_price = CALCULATE(

minx(Sheet1,Sheet1[Total Amount]),

Sheet1[Colour] = get_all_colours

)

return get_min_price

 

[johnt75 · Accepted solution]

You can create a measure like

Cheapest colour =
var summaryTable = CALCULATETABLE( SUMMARIZE('Table', 'Table'[Car], 'Table'[Colour], 'Table'[Amount]), REMOVEFILTERS('Table'[Colour]))
var cheapestColours = TOPN(1, summaryTable, [Amount], ASC)
return CONCATENATEX( cheapestColours, [Colour], ", ")

The CONCATENATEX is in case there are multiple colours at the same price

[zausten]

thanks @johnt75   that looks like its working as expected...now to apply to my actual complicated dataset!