cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Helper I

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

Announcements