Finding minimum rank per date

[rahuldas_vgm]

Hello,

I am currently stuck with a problem. I have a rank column which ranks Columns with ID as per the ranking list. I wanted to find out the best rank as per the day. If the same ID has 3 different ranks in a day it should show me the best rank for that day. Instead what it is doing is, giving me the best rank from all the dates of its entry. Here are the DAX I'm Using:

Best Rank = CALCULATE(IF(SELECTEDVALUE('All Detailed APR'[Status])="ANSWERED",CALCULATE(MIN('All Detailed APR'[Rank]),ALLEXCEPT('All Detailed APR','All Detailed APR'[ID Number])),8))

Thank You for your help!

[rahuldas_vgm · Accepted solution]

Thank you for your help @Mahesh0016 .I had forgotten to put DISTINCT(File Date) in the formula to get the minimum rank for each date. The thing worked as soon as I changed the formula to:

Best Rank = CALCULATE(IF(SELECTEDVALUE('All Detailed APR'[Status])="ANSWERED",CALCULATE(MIN('All Detailed APR'[Rank]),

DISTINCT('All Detailed APR'[File Date]),ALLEXCEPT('All Detailed APR','All Detailed APR'[ID Number])),8))

[rahuldas_vgm]

I am trying to find the Best Rank for each date and not the best rank (minimum value) amongst all the dates. Like for File Date value 8th December the minimum is 2 as the two values are 2 and 8 so minimum for that day is 2 instead of 1 which is minimum amongst all the dates. Something like this:

[Mahesh0016]

>>Create Calculated column.

BestRank = CALCULATE(MIN('File Date'[Rank]),ALLEXCEPT('File Date','File Date'[File Date]))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!