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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Dave_Gugg
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 = 

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?

3 REPLIES 3
v-huizhn-msft
Employee
Employee

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


1.PNG

 

Best Regards,
Angelia

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.