Concatenate user order data into field, ordered by date

[MarcusR1]

Hi,
I have a Users table and a Orders table (connected via UserID) and I would like to produce a measure that returns a text string for the top 3 previous orders for each User that looks like:
OrderDate|OrderID|OrderValue|OrderDate|OrderID|OrderValue|OrderDate|OrderID|OrderValue

eg
22/02/2023|296451|£637.10|30/01/2023|295674|£138.75|25/01/2023|295514|£362.49
The string should be in OrderDate order DESC (ie, newest first)

I can then use PATH() to seperate out the values, or use the whole string as a Tool Tip in a graphic report.

Can anyone help me?

[MarcusR1]

Hi @v-zhangti Charlotte, Thank you for your reply. I have applied your Measures to my data but it is showing more than the last three orders - my data also has ORDER_ITEMS table. I can't see where I can upload a .PIBX file for you to see.

[v-zhangti]

Hi, @MarcusR1 

You can provide some sample data, as well as the output you expect. Sensitive information can be removed in advance.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarcusR1]

@v-zhangti you helped me with a similar issue, can you help here?

[MarcusR1]

UPDATE:
So I thought I'd pull the data from each of the tables and put it into a PATH() so that I could them pull them all together using PATHITEM() from each one (I know it's convoluted, but I'm clutching at straws here!)
The OrderID and OrderDate work fine:

But the Order Value is pulling through the sum of the entire USER spend, not just the last three orders.

This is what I'm using:

3 OrderValue = sumx(FILTER(ORDER_ITEMS,ORDER_ITEMS[OrderID] = PATHITEM('USERS'[3 OrderID],1,INTEGER)),ORDER_ITEMS[TotalPrice])

and to check that I am using the correct data, I paste in the actual order number from the OrderID above into the formula:
3 OrderValue = sumx(FILTER(ORDER_ITEMS,ORDER_ITEMS[OrderID] = 296451),ORDER_ITEMS[TotalPrice])

And it works, 637 is the correct sum of OrderID 296451 (made up of 3 cart items) for this USER; but I need the OrderID to be pulled dynamically from the PATHITEM()
But adding the dynamic part (which works for OrderDate and OrderID) doesn't work with the filter

3 OrderValue = sumx(FILTER(ORDER_ITEMS,ORDER_ITEMS[OrderID] = PATHITEM('USERS'[3 OrderID],1,INTEGER)),ORDER_ITEMS[TotalPrice])

I've tried adding Int() Value() and format() to the formula in all possible combinations on both sides of the comparison, but it just gives various versions of the same message saying the types are incompatible.

3 OrderValue = sumx(FILTER(ORDER_ITEMS,int(value(format(ORDER_ITEMS[OrderID],"General Number"))) = int(value(format(PATHITEM('USERS'[3 OrderID],1,INTEGER),"General Number")))),ORDER_ITEMS[TotalPrice])

Error Message:
MdxScript(Model) (144, 130) Calculation error in measure 'USERS'[3 OrderValue]: Cannot convert value '' of type Text to type Number.

I did notice in the SUMX documentation https://learn.microsoft.com/en-us/dax/sumx-function-dax  examples, that the only example similar to this used a STATIC value for the comparison (like I did with the OrderID above) hard-coding the SalessTerritory ID of 5 into the formula
= SUMX(FILTER(InternetSales, InternetSales[SalesTerritoryID]=5),[Freight])

Is this a bug in SUMX() or FILTER(), or is it a known restriction? It's as if the PATHITEM('USERS'[3 OrderID],1,INTEGER) is not being passed to the FILTER() correctly.
If it is a known restriction, it seems strange to have a formula that only allows hard-coded parameters......

[v-zhangti]

Hi, @MarcusR1 

Is your source data like this?

Rank date = RANKX(ALL('Orders table'),CALCULATE(MAX('Orders table'[OrderDate])),,DESC)

Measure:

3 OrderDate = 
CONCATENATEX (
    CALCULATETABLE ( VALUES ( 'Orders table'[OrderDate] ),
        FILTER ( ALL ( 'Orders table'),[Rank date]<=3 )),
    [OrderDate],
    "|"
)

3 OrderID = 
CONCATENATEX (
    CALCULATETABLE ( VALUES ( 'Orders table'[OrderID] ),
        FILTER ( ALL ( 'Orders table'),[Rank date]<=3 )),
    [OrderID],
    "|"
)

3 OrderValue = 
CONCATENATEX (
    CALCULATETABLE ( VALUES ( 'Orders table'[OrderValue]),
        FILTER ( ALL ( 'Orders table'),[Rank date]<=3 )),
    [OrderValue],
    "|"
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarcusR1]

hi Charlotte @v-zhangti thank you for your suggestion, but the Order ID and the Order Value do not correspond. If you look at your example table you'll see that ID 296451 is the last in the list of IDs (295514|295674|296451) but it's value (637.10) is the first in the list 637.1|138.75|362.49 - I think this is becasue the Rank date measure works on the date and ID but not the value.
The other issue I have is that with your formulae acting on 100000+ orders, PowerBI runs out of memory!