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

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply

Using the pickup date to rank orderid and sub-orderid as rank 1

In my orders table, I have the order_id and a sub-order id created if the customer has sent a Laptop along with his/her Mobile which is our main service. Both orders have the exact pickup date, but different order_ID. 

I need to know whether the first order of all our customers has a laptop included. I did order ranking through this DAX formula: 

Order Ranking 1 =
VAR _currentcustomer = Orders[customer_id]
VAR _newtable =
    FILTER (
        Orders,
        Orders[customer_id] = _currentcustomer )

VAR _rankingresult =
    RANKX ( _newtable, orders[time_pickup_id].[Date],, ASC )

RETURN

_rankingresult

All sub-order id shows as second order being sub-order id, hence, I am unable to extract the accurate number of first orders with a laptop. 

I was told that I could use the pickup date as a parameter to rank both order_id and sub-order id as rank 1 so that I could pull up the right number. I don't know how to do it. 

I need help! 

My customer table has a one-to-many relationship with my orders table. 
4 REPLIES 4

@amitchandak it didn't work. 

Maybe I asked the question wrong. 
How about how can I filter the orders table where if order rank 1 and order rank 2 have the same pickup date, then sum the laptop contribution?

amitchandak
Super User
Super User

@Andrew_na_lang ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Is there any blank value, not sure why you are getting rank as 2

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak here's a sample table. 

The goal is to mark all customers with LAPTOP in their first order, be it main order or sub-order id. 

Screenshot 2023-01-17 195316.png

@Andrew_na_lang , Try like

 

RANKX ( Filter(Orders, Orders[customer_id] = earlier(Orders[customer_id]) && [Laptop_contribution] =0 && not(isblank([Laptop_contribution] )) ), orders[time_pickup_id].[Date],, ASC )

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

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

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.