Get TOP 10 most commented videos by period

[amirabedhiafi]

I have the following query to calculate the TOP 10 commented videos for a specific month and year  : 

SELECT TOP 10 videoId
,COUNT(commentId) AS CountComments FROM dbo.VideoComments
WHERE MONTH(publishedAt)=12
AND YEAR(publishedAt)=2020
GROUP BY
videoId
,MONTH(publishedAt)
ORDER BY countcomments DESC

I want to implement the same logic using DAX  where Month and Year are filters.

[amitchandak · Accepted solution]

@amirabedhiafi , you can use TOPN for that

Better to one field month year , assume you have measure -CountComments 

Top 10 City Rank = CALCULATE([CountComments ],TOPN(10,all(Table[Month Year]),[CountComments ],DESC),VALUES(Table[Month Year))

TOPN -https://www.youtube.com/watch?v=QIVEFp-QiOk

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[vanessafvg]

are you table to provide some sample data?