Forum Discussion
First Offer Employee
Hi all,
I can't solve the following Problem: I have a table with all document position (orders, offers, invoices etc), called BI_Positionen_Doc. It is constructed as following:
| Article | Documenttype | DocumentNo | Position | Salesman | DocDate |
| xyz | offer | 102 | 1 | Georg | 05.01.2022 |
| abc | offer | 102 | 2 | Georg | 05.01.2022 |
| xyz | order | 500 | 1 | Mark | 10.01.2022 |
I want to add a new column that states for each line the first offer Date and the Employee who did the first offer. Aim is to get the first employee who offered the article.
So adding to the table above f.e.:
| FirstOfferDate | FirstOfferEmployee |
| 05.01.2022 | Georg |
| 05.01.2022 | Georg |
| 05.01.2022 | Georg |
The date I could solve with this:
FirstofferDate= CALCULATE(MINX(TOPN(1,BI_Positionen_Doc,BI_Positionen_Doc[Date],1),BI_Positionen_Doc[Date]), FILTER(BI_Positionen_Doc,BI_Positionen_Doc[discriminator]="Offer"))
Example: For the article 12191020023 the first offer date was 07.01.2016 (see third column) - the formula worked here (see second column):
But I can't solve how to get the first employee?
Could you help me please?
Thanks and best regards
Eileen
6 Replies
Does it have to be DAX or can this be done in Power Query?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqqisUtJRyk9LSy0C0oYGRiASiN1T84vSgbSBqZ6BoZ6RgZGRUqxOtFJiUjKGciPcyqGmF6WAlZsaGEBN900sygbrR6iOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Article = _t, Documenttype = _t, DocumentNo = _t, Position = _t, Salesperson = _t, DocDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Article", type text}, {"Documenttype", type text}, {"DocumentNo", Int64.Type}, {"Position", Int64.Type}, {"Salesperson", type text}, {"DocDate", type date}},"de"), #"Added Custom" = Table.AddColumn(#"Changed Type", "FirstOfferDate", (k)=> Table.Sort(Table.SelectRows(#"Changed Type",each [Article]=k[Article] and [Documenttype]="offer"),{{"DocDate", Order.Ascending}}){0}[DocDate],type date), #"Added Custom1" = Table.AddColumn(#"Added Custom", "FirstOfferEmployee",(k)=> Table.Sort(Table.SelectRows(#"Changed Type",each [Article]=k[Article] and [Documenttype]="offer"),{{"DocDate", Order.Ascending}}){0}[Salesperson],type text) in #"Added Custom1"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".
- Modify the Source= step to point to your table instead.
