Return Second highest value from a summarize Table

[Jagan_MFilterIT]

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_1	Count
Taste/flavour	46867
Quantity	3495
Mfg. & Expiry	115
Price	1161
Packaging	231
Quality	3911
Miscellaneous	178
Delivery	3907
Platform Services	82
Genuineness	524
health	33
Stock Availability
Gifting
Usability
Duplicate Pack
Expiry
Missing Material
Hygiene	1

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. 

[amitchandak · Accepted solution]

@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]

Thanks a lot Sir, This worked! 

[amitchandak · Accepted solution]

@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]

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 · Accepted solution]

@Jagan_MFilterIT , Try like

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