Count Sequence Number Using DirectQuery

[Dave_Gugg]

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 = 

COUNTROWS(

     FILTER(

          CALCULATETABLE(

               Demand,

               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?

[v-huizhn-msft]

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,
Angelia

[Dave_Gugg]

Hi Angelia

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

Thank you

[v-huizhn-msft]

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,
Angelia