Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ShawnPrice
Helper I
Helper I

Show most recent information

Below is a memo field I have in our AX 2009 system and I need to be able only show the most current update which should be the top update for each sales person. When I add the memo field in BI I get all of the data in this field. I tried using the First & Last feature in values but it didn't work.  Thanks for the help.

 

 

 

12/08/16 - Sharon still in process of deciding where remaining printers will be shipped to. Once addresses provided to HP, PO's will be cut to TROY. - BM

 

11/15/16 - Glen working on getting final PO's cut to us for remaining printers that he did not have shipping addresses for. - BM

 

09/30/16 - Glen advises that the customer has cut both the hardware and consumables PO's. - BM

 

09/15/16 - Glen advises that HP will sell the hardware directly and the consumables will be put on MWBE paper with indirect Big Deal through HP Source. Working with Glen and team to find best way to drop ship all of the product that will be ordered via multiple PO's. Customer still expecting to cut PO's by September 30. - BM

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

Hi @ShawnPrice,

Firstly, in query editor, add a custom column using the following formula.

Table.AddColumn(Source,"splitcolumn", each Table.FromColumns({Text.Split([Memo], "BM")}))

1.PNG

Secondly, expand your column in query editor and then add the following step in Advanced Editor.

 #"Split Custom.splitcolumn" = Table.SplitColumn(#"Expanded Custom.splitcolumn" ,"Custom.splitcolumn.Column1", Splitter.SplitTextByDelimiter("-"),2)
2.PNG

Thirdly, trim the date column as shown in the following screenshot and change its type to Date.
3.PNG

5.PNG

Apply these changes in Power BI Desktop, then create a column using the formula below and create a report as shown in the following screenshot.

RankColumn = RANKX(FILTER(Table3,EARLIER(Table3[owner])=Table3[owner]),Table3[Custom.splitcolumn.Column1.1])

6.PNG


For more details, please review attached PBIX file.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yuezhe-msft
Employee
Employee

Hi @ShawnPrice,

Firstly, in query editor, add a custom column using the following formula.

Table.AddColumn(Source,"splitcolumn", each Table.FromColumns({Text.Split([Memo], "BM")}))

1.PNG

Secondly, expand your column in query editor and then add the following step in Advanced Editor.

 #"Split Custom.splitcolumn" = Table.SplitColumn(#"Expanded Custom.splitcolumn" ,"Custom.splitcolumn.Column1", Splitter.SplitTextByDelimiter("-"),2)
2.PNG

Thirdly, trim the date column as shown in the following screenshot and change its type to Date.
3.PNG

5.PNG

Apply these changes in Power BI Desktop, then create a column using the formula below and create a report as shown in the following screenshot.

RankColumn = RANKX(FILTER(Table3,EARLIER(Table3[owner])=Table3[owner]),Table3[Custom.splitcolumn.Column1.1])

6.PNG


For more details, please review attached PBIX file.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @ShawnPrice, how is the Sales Person captured in the data? Is there a field with the Sales Person's name, or is it simply that their name is included in the memo field? Is there a specific field for the memo date?

 

I would try using the Top N filter on the Memo Date field to return the top 1 memo by most recent date.

This is the snapshot of one record in the database.

Capture.PNG

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors