Forum Discussion

Jagan_MFilterIT's avatar
Jagan_MFilterIT
Helper 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 Query: 

 

Positive_Theme_1 =
MAXX(
TOPN(
1,
SUMMARIZE('00. ITC_Comment','00. ITC_Comment'[theme_1],"Count", CALCULATE('4. Comments_Measures'[review count],'00. ITC_Comment'[comment_sentiment_value] ="Positive")),
[count],DESC),
[theme_1]
)
 
Summary table also looks like below:
 
theme_1Count
Taste/flavour46867
Quantity3495
Mfg. & Expiry115
Price1161
Packaging231
Quality3911
Miscellaneous178
Delivery3907
Platform Services82
Genuineness524
health33
Stock Availability 
Gifting 
Usability 
Duplicate Pack 
Expiry 
Missing Material 
Hygiene1

 

the first dax query that I have given above returns "Taste/flavour". Now if I want second and third themes based on the count, how should I update the Query. is there any way to get this through 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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    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

    • Jagan_MFilterIT's avatar
      Jagan_MFilterIT
      Helper 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
        Super 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])