Forum Discussion

thebaguette's avatar
thebaguette
New Member
4 years ago

Get dimension value based on minimum metric value

Hi,

 

I have the following table :

 

EANStorePriceDate
AAAAAAmazon10001/01/2022
AAAAAEbay12001/01/2022
AAAAAAmazon11008/01/2022
AAAAAEbay13008/01/2022
AAAAAAli10508/01/2022
CCCCCAmazon1001/01/2022
CCCCCEbay1201/01/2022
CCCCCAmazon1008/01/2022
CCCCCAli1108/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:

 

EANCheapest_store
AAAAAAli
CCCCCAmazon

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):

StoreCount_cheapest
Ali1
Amazon1
Ebay0

 

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

2 Replies