Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All,
My business case is the following :
I have orders identified by a specific order_id and each order can be received in several sequences (reception_id) with different reception dates. I want to identify whether or not for each order_id, this is the first reception (earliest date).
In DAX, I have created the following formula which works perfectly and I would like to translate it into M language.
first_reception = IF('TABLE'[reception_date]<=CALCULATE(MIN('TABLE'[reception_date]);ALLEXCEPT('TABLE';'TABLE'[order_id]));"YES";"NO")
The dataset :
order_id | reception_id | reception_date |
O001 | R001 | 01/01/2019 |
O001 | R002 | 01/01/2019 |
O001 | R003 | 15/02/2019 |
O002 | R004 | 12/02/2019 |
O002 | R005 | 13/02/2019 |
O002 | R006 | 14/02/2019 |
O002 | R007 | 15/02/2019 |
O003 | R008 | 16/02/2019 |
O004 | R009 | 17/02/2019 |
O005 | R010 | 18/02/2019 |
O006 | R011 | 19/02/2019 |
Solved! Go to Solution.
Hi,
I have pasted your data into an excel sheet and loaded it into power bi and edited the Power Query to identify the first reception date. Given below is the Power Query.
let Source = Excel.Workbook(File.Contents("C:\Users\R\Orders.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"order_id", type text}, {"reception_id", type text}, {"reception_date", type date}}), //added codes starts here.. TempTable = #"Changed Type", Orders = #"Changed Type", //this function X filters the table based on the parameter "order_id" X = (y as table,z as text ) as table => Table.SelectRows(y,each Record.Field(_,"order_id")=z), //Adds the column 'first_reception' to the table. AddColumn = Table.AddColumn( Orders, "first_reception", each Record.Field(_,"reception_date") = List.Min( // comparison of current row's reception date with the minimum of all receptions dates of that order. Table.Column( //Table.Column converts the column to a list which is passed on to List.Min() X(TempTable,Record.Field(_,"order_id")),"reception_date" // filters the table bsed on current record's order_id and passing it on to Table.Column() funciton ) ) ) in AddColumn
Thanks both of you for the proposed solution. It works perfectly. Cheers
Hi,
I have pasted your data into an excel sheet and loaded it into power bi and edited the Power Query to identify the first reception date. Given below is the Power Query.
let Source = Excel.Workbook(File.Contents("C:\Users\R\Orders.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"order_id", type text}, {"reception_id", type text}, {"reception_date", type date}}), //added codes starts here.. TempTable = #"Changed Type", Orders = #"Changed Type", //this function X filters the table based on the parameter "order_id" X = (y as table,z as text ) as table => Table.SelectRows(y,each Record.Field(_,"order_id")=z), //Adds the column 'first_reception' to the table. AddColumn = Table.AddColumn( Orders, "first_reception", each Record.Field(_,"reception_date") = List.Min( // comparison of current row's reception date with the minimum of all receptions dates of that order. Table.Column( //Table.Column converts the column to a list which is passed on to List.Min() X(TempTable,Record.Field(_,"order_id")),"reception_date" // filters the table bsed on current record's order_id and passing it on to Table.Column() funciton ) ) ) in AddColumn
You can use this Custom Column
let myorderid=[order_id] in List.Min(Table.SelectRows(#"Previous Step Name",each [order_id]=myorderid)[reception_date])=[reception_date]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |