How to find first order info per user id

[EnricoGiordano]

Hello everyone,

is there a simple way to find and calculate, for each customer of my table, the first order date and the correlated information? Such as the quantity and the price he paid?

I have a table like this:

profile_id

order_date

quantity

price

Thanks!

[Ahmedx]

and this in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5BDsAgCAT/wtkYWKvx2vYZxv9/Q5DUNBzYTRgWGIOkZa4ZjEKJRKuaQ+WmmZTjx63dzFnlcd5DfjufPDjkr2//61wC31npZ4BLOGBDgD8wFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [order_date = _t, customer_id = _t, quantity = _t, #"revenue €" = _t, product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"order_date", type date}, {"customer_id", Int64.Type}, {"quantity", Int64.Type}, {"revenue €", Int64.Type}, {"product", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"customer_id"}, {{"Count", (x)=> Table.SelectRows(x, (y)=> y[order_date] = List.Min(x[order_date]) )
}}),
    #"Removed Other Columns" = Table.Combine( Table.SelectColumns(#"Grouped Rows",{"Count"})[Count])
in
    #"Removed Other Columns"

[Ahmedx · Accepted solution]

try this

[Anonymous]

Hi @EnricoGiordano ,

I suggest you to create a measure to filter your visual.

My Sample:

Filter data on First Order Date = 
VAR _FIRSTORDER = CALCULATE(MIN('Table'[order_date]),ALLEXCEPT('Table','Table'[profile_id]))
RETURN
if(MAX('Table'[order_date]) = _FIRSTORDER,1,0)

Add this measure into visual level filter and set it to show items when value = 1.

Best Regards,
Rico Zhou

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

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[EnricoGiordano]

Hi, yes sure this is an example of the starting table from which I need to extract the info explained above:

My need is actually to create a new table in Power BI from that starting one, which only shows for each profile_id (customer) the first service date and the related info to this specific order (price_gross, quantity and product ordered).

The solution provided is partially correct for me, but I would like to know if it's possibile to create a new table for this analysis too, starting from the initially order table.

Thank you

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[EnricoGiordano]

Hi, here again the sample data that covers my issue.

In my power bi dataset i have a table that contains all the orders done by each customers, based on the order date. Each row represents a single order transaction made by a specific customer, with information about the product and the sale order in €.

order_date	customer_id	quantity	revenue €	product
16/05/2023	1	5	12 €	A
12/05/2023	2	6	20 €	B
18/05/2023	1	3	10 €	A
20/05/2023	2	4	12 €	C
21/05/2023	2	10	18 €	C
03/05/2023	1	2	22€	B

My issue is to extract and create from this table above a new table, showing only the rows of the first order made by each customer, as following:

first_order_date	customer_id	first_quantity	first_revenue €	first_product
03/05/2023	1	2	22 €	B
12/05/2023	2	6	20 €	B

Is it clear now?

Thenk you very much,

Enrico

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTTNzDVNzIwMlbSUTIEYlMQbaTwqGkNkOGoFKsDVGOEpMYIiM1AtAFUjRNEjQWaOWDaAMUcIwM0c0yQ7XKGqDFEU2NoACIsUBQZGKNZBlJoZAR3TywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [order_date = _t, customer_id = _t, quantity = _t, #"revenue €" = _t, product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"order_date", type date}},"es"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"customer_id"}, {{"Rows", each _, type table [order_date=nullable date, customer_id=nullable text, quantity=nullable text, #"revenue €"=nullable text, product=nullable text]}}),
    #"Replaced Value" = Table.ReplaceValue(#"Grouped Rows",each [Rows],each Table.FirstN(Table.Sort([Rows],{{"order_date", Order.Ascending}}),1),Replacer.ReplaceValue,{"Rows"}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Replaced Value", "Rows", {"order_date", "quantity", "revenue €", "product"}, {"order_date", "quantity", "revenue €", "product"})
in
    #"Expanded Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".