Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Count Sequence Number Using DirectQuery

I need to create a custom column on my fact table that will hold the customer sequence number of that order (for example, first order, second order, etc.).  I am feeling boxed in because I'm using DirectQuery and am not able to use any COUNT function.  I know the basic logic would be count every order number for the current customer where the order date <= the current order's date.  I was playing around with this, but was unable to make it work due to the DirectQuery limitation:

Customer Order Sequence = 





               ALLEXCEPT( Demand, Demand[CustomerKey])


          Demand[Order Date] < EARLIER( Demand[Order Date] )

               || (Demand[Order Date] = EARLIER (Demand[Order Date])




Does anyone have any ideas on how to get this custom column?


Hi @Dave_Gugg,

>>where the order date <= the current order's date.

Your the order date is increasing, "the current order's date" is the lasted date in the table, right?

Based on my test, you can program the SQL Statement to calculate order number for the current customer where the order date <= the current order's date. I transfer your DAX into SQL as follows. Please test it in SQL Server Management Studio (SSMS), if it works fine, you can type it in Advanced options window->click OK->Load, you will get the expected result.


DECLARE @mm date
SELECT @mm=MAX(A.OrderDate) FROM Demand A
SELECT A.CustomerKey, COUNT(*)AS result
FROM Demand A
Where A.OrderDate<= @mm
GROUP BY A.CustomerKey



Best Regards,


Hi Angelia


I'm aware of how to get this using TSQL.  Is there a way to get it using DAX?


Thank you

Hi @Dave_Gugg,

COUNTROWS function is unable to use in DirectQuery model. I will reproduce it on my local computer, I will post the update if I find solution.


Best Regards,

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors