Forum Discussion

DZet's avatar
DZet
New Member
3 years ago

Issue with DAX calculation (RANK) on a specific aggregation level

Hi guys! I guess my problem was solved on the Internet already at least "a few" times, but I hope you'll be understanding. Here is the thing: We have: -Sales (fact table) with OrderNo, OrderedItem...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi DZet ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create  a measure. 

Measure = var _sellrank = SELECTEDVALUE('Sales'[SellRank])
var _t =DISTINCT(SELECTCOLUMNS( FILTER(ALLSELECTED( 'Sales') , 'Sales'[SellRank] <= _sellrank) ,"rank" , [SellRank]))
return
COUNTROWS(_t)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

  • DZet's avatar
    DZet
    New Member
    3 years ago

    Hi!

    Unfortunately, it's still not correct. As I described, this rank should be kind of YTD, so limited not straight by the slicer, but by such date range:

    var MaxDt = MAX(DateDim[Date])
var MinDt = DATE(YEAR(MaxDt), 1, 1)

    The slicer limits MaxDt, but we create ranking starting from Jan 1st. And that's the hardest part, I don't know how to change this filter context.