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
Anonymous
Not applicable

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:

ArticleDocumenttypeDocumentNoPositionSalesmanDocDate
xyzoffer1021

Georg

05.01.2022
abcoffer1022Georg05.01.2022
xyzorder5001Mark10.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.:

FirstOfferDateFirstOfferEmployee
05.01.2022

Georg

05.01.2022Georg
05.01.2022Georg

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):
 DinEileen_1-1698915718914.png

 

But I can't solve how to get the first employee?

 

Could you help me please?

 

Thanks and best regards

Eileen


 

 

6 REPLIES 6
lbendlin
Super User
Super User

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".

 

Anonymous
Not applicable

Hi Power Query would be also fine. But the code you gave me just gives me the example I posted here as a table? This was only an example...my table BI_Position_Doc has loads of data. I don't know how this formula can help me...

Thanks!

 

Modify the Source= step to point to your table instead.
Anonymous
Not applicable

ok so: Source=Table.FromRows(BI_Positionen_Doc),

#Changed Type etc.....

 

sorry but as i don't understand teh code, it is hard to just replace something...

Source = BI_Positionen_Doc,

Anonymous
Not applicable

Sorry...still doesn't work...but thanks for your help

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.