Forum Discussion

Jagan_MFilterIT's avatar
Jagan_MFilterIT
Icon for Helper I rankHelper I
4 years ago
Solved

Return Second highest value from a summarize Table

Hi,    I have a requirement where I want values of products which had first and second highest reviews. For getting the product which has the highest number of reviews I am using the following Dax ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Jagan_MFilterIT , Create measure like these

     

    Measure = CALCULATE('4. Comments_Measures'[review count],filter('00. ITC_Comment','00. ITC_Comment'[comment_sentiment_value] ="Positive"))

     

    Rank = rankx(allselected('00. ITC_Comment','00. ITC_Comment'[theme_1]), [measure],,desc,dense)

     

    Rank 2= sumx(filter(values('00. ITC_Comment','00. ITC_Comment'[theme_1]), [Rank] =2), [Measure])

     

    either you can filter Rank at visual or use a measure like Rank 2

  • amitchandak's avatar
    amitchandak
    4 years ago

    Jagan_MFilterIT , Try like

    Rank 2= maxx(filter(values('00. ITC_Comment','00. ITC_Comment'[theme_1]), [Rank] =2), [theme_1])

Jagan_MFilterIT's avatar
Jagan_MFilterIT
Icon for Helper I rankHelper I
4 years ago

Hi Sir, 

 

Thanks a lot for the reply. The Rank2 formula given above gives the numeric value of the row "3495" but I want the corresponding theme(label) = "Quantity". Is there a way to get it

 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Jagan_MFilterIT , Try like

Rank 2= maxx(filter(values('00. ITC_Comment','00. ITC_Comment'[theme_1]), [Rank] =2), [theme_1])