Get dimension value based on minimum metric value

[thebaguette]

Hi,

I have the following table :

EAN	Store	Price	Date
AAAAA	Amazon	100	01/01/2022
AAAAA	Ebay	120	01/01/2022
AAAAA	Amazon	110	08/01/2022
AAAAA	Ebay	130	08/01/2022
AAAAA	Ali	105	08/01/2022
CCCCC	Amazon	10	01/01/2022
CCCCC	Ebay	12	01/01/2022
CCCCC	Amazon	10	08/01/2022
CCCCC	Ali	11	08/01/2022

EAN is the reference of a product selled by Amazon, Ali... I have the product price updated every week. I need to know, for a disctinct product, what is the store selling the product at the cheapest price.

 For example, if the date filter is all January, the results should be:

EAN	Cheapest_store
AAAAA	Ali
CCCCC	Amazon

As you can see, only the last updated date should be taken into account (if there is data).

I would like also to be able to count the number of time each store has been the cheapest one (based on last date as well):

Store	Count_cheapest
Ali	1
Amazon	1
Ebay	0

I tried the following dax formula but it does not give the expected results:

'''

var maxDate = CALCULATE(MAX(full_pricing_stores[date]))
var min_Price = [min_price_concurrents]

RETURN
CALCULATE (
COUNTROWS (full_pricing_stores),
FILTER( full_pricing_stores, full_pricing_stores[date] = maxDate && full_pricing_stores[price] = min_Price),
ALLEXCEPT(full_pricing_stores, full_pricing_stores[store] )
)​

 '''

Any helpd & advice would be greatly appreciated 🙂

Thank you

[amitchandak]

@thebaguette , Try a measure like

Rankx(Filter(allselected(Table[EAN], Table[Month Year], Table[Store]), [EAN] = Max([EAN]) && [Month Year] = max([Month Year])), calculate(Min(Table[Price])),,asc,dense)

you can use visual level filter of 1

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[thebaguette]

Hi @amitchandak,

Thank you for your answer. Unfortunately it didn't work. I did some calculation before connecting the table to PBI to make it work 🙂