Forum Discussion

riguberto's avatar
riguberto
Frequent Visitor
6 years ago
Solved

Get top n value considering duplicate values

Hello,    I'm trying to set 3 different dax metrics to get the 1st,2nd,3rd values of a product price (for diferents urls). product_id url_id LastPrice product_price InvalidPrice 158946 ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

riguberto , Try like

 

rank =	rankx(FILTER(priceregistrationraw,and(NOT(priceregistrationraw[InvalidPrice]=1),priceregistrationraw[LastPrice]=1)),[price],,ASC,Dense)

Second Best Price = 
sumx(filter(priceregistrationraw,[Rank]=2),CALCULATE ( min ( priceregistrationraw[price])))

 

riguberto's avatar
riguberto
Frequent Visitor
6 years ago

Thanks for your response. 

 

rank... creating this metric pop up the error: can't determine the value of price. Maybe the column doesn't exist or it is no rows (translating myseflt from spanish... :S)

 

 

  • riguberto's avatar
    riguberto
    Frequent Visitor
    6 years ago

    ok, I didn't understood that rank was a column in the priceregistrationraw table (I was trying as a metric (I'm totally newby)).

     

    My problem now is that the table has more products_id and they are getting ranked all together. I tried to put a product_id filter, but now the filter for lastprice and isvalid are not working (the product_id looks fine)...

     

    What I tried

    rank = rankx(FILTER(priceregistrationraw,and(NOT(priceregistrationraw[InvalidPrice]=1),AND(priceregistrationraw[LastPrice]=1,priceregistrationraw[product_id]=EARLIER(priceregistrationraw[product_id])))),[total_price_numeric],,ASC,Dense)