Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
DZet
New Member

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, OrderedItemKey, OrderDt, CustomerKey, and Amount.

More than one row (OrderedItem) may be in the same OrderNo.
There is a slicer for dates in the report, a slider of the between type, to set the max/min of the date range.

The task is to display the TOP 5 customers sorted by the highest order amount (not total ordered amount) in the selected year up until the selected date.

 

I'd love to see the proper and "elegant" solution.
I've created a precalculated table to aggregate facts for the Order level and tried to use the RANKX function, but I'm not able to apply the date filter properly, to get a kind of YTD range (max date from the slicer and min as Jan 1st of the same year).
I'd be grateful for any help! 🙂

5 REPLIES 5
Anonymous
Not applicable

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.

vtangjiemsft_0-1667900140504.png

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. 

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.

Anonymous
Not applicable

Hi @DZet ,

 

You can try to add multiple fields to a hierarchy slicer.

Please refer to the following document for more information:

Slicers in Power BI - Power BI | Microsoft Learn

Add multiple fields to a hierarchy slicer - Power BI | Microsoft Learn

 

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. 

 

Jayee
Responsive Resident
Responsive Resident

Hi @DZet ,

 

Try this not 100% sure about result if not work , can you share the .pbix file (without sensitive data) to us.

Testing =
RANKX (
FILTER (
ALL (
'Table'[OrderedItemKey],
'Table'[OrderNo],
'Table'[OrderDt],
'Table'[CustomerKey]
),
'Table'[OrderedItemKey] = MAX ( 'Table'[OrderedItemKey] )
),
TOTALYTD ( SUM ( 'Table'[Amount] ) ,'Date'[OrderDt])
)

 

Hi Jayee,

thanks for your help, but it's not correct. Item key is not important, we need to rank the sell on the Order level.
Here is the link to the pbix file: https://www.dropbox.com/s/7jp7ke7fxmpyk78/PBI%20Task.pbix?dl=0

 

I created a measure with your code and you can see it in the table on the left side. On the right, there is a SellRank, that I created, but doesn't work with the bottom date range properly.

Thanks in advance to anyone who could help! 🙂

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.