topic Re: Using the pickup date to rank orderid and sub-orderid as rank 1 in DAX Commands and Tips

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

Andrew_na_lang — Tue, 17 Jan 2023 14:28:48 GMT

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.

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

amitchandak — Tue, 17 Jan 2023 15:19:01 GMT

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

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

Andrew_na_lang — Tue, 17 Jan 2023 15:54:33 GMT

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.

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

amitchandak — Tue, 17 Jan 2023 16:05:05 GMT

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 )

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

Andrew_na_lang — Tue, 17 Jan 2023 18:30:30 GMT

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?