topic Custom Column please help!!!! in DAX Commands and Tips

Custom Column please help!!!!

Mattym80 — Thu, 08 Sep 2022 19:23:42 GMT

Hi

Im hoping someone can help please!!!

I am trying to create 2 custom columns please see example sheet below.

I am wanting to return the max date when the "Postion" is the same value and < index number (doesnt have to have an index column thats just the way I was trying to solve it just has to be a prior date)

I am also wanting to return the ID also were there is a prioir date for that postion and date match

date	Postion	ID	Index	Column 1 Return Date	Column 2 retun ID
01/02/2022	1	1	1
01/02/2022	2	1	2
01/02/2022	3	1	3
01/02/2022	4	2	4
01/02/2022	5	2	5
02/02/2022	6	1	6
02/02/2022	1	1	7	01/02/2022	1
02/02/2022	7	1	8
02/02/2022	8	1	9
02/02/2022	2	3	10	01/02/2022	2
02/02/2022	9	3	11
03/02/2022	1	3	12	02/02/2022	1
03/02/2022	2	1	13	02/02/2022	3
03/02/2022	20	1	14
04/02/2022	24	2	15
04/02/2022	25	2	16
04/02/2022	3	2	17	01/02/2022	1

Re: Custom Column please help!!!!

Anonymous — Fri, 09 Sep 2022 05:51:55 GMT

Hi Mattym80

According to your needs , I created two calculated columns .

Column = CALCULATE(MAX('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[Postion]),'Table'[date]<EARLIER('Table'[date])))

Column 2 = var _date='Table'[Column] RETURN CALCULATE(MAX('Table'[ID]),FILTER(ALLEXCEPT('Table','Table'[Postion]),'Table'[date]= _date))

Add these two columns to your table , you will get a result like below .

In your result ,when "Postion" = 2 , "ID" = 3 , the return value should be 1 .

I have attached my pbix file , you can refer to it .

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

Re: Custom Column please help!!!!

latimeria — Fri, 09 Sep 2022 07:04:08 GMT

Hi Mattym80 ,

In PowerQuery,

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFLDsMgDESvErGOgjGfJBfoJSIWrdQLROqit29obSuI4Ep4gfzGeJhtM+CG2/Oxv+77e0BANKNxpyonj5cYo6hjnjCvY4EmBR2LhMUzhg2W6NGkY+xyPgqcBZws4kStjmYmzaKPXghbdUx+CHgFK52OZGVJlY+/NPflyuWYW0bX5lqNfIr/aVDW8V0JsKaKLrQgR+ziH5BDdkkHPXOSnxXvOX8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, Postion = _t, ID = _t, Index = _t, #"Column 1 Return Date" = _t, #"Column 2 retun ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"Postion", Int64.Type}, {"ID", Int64.Type}, {"Index", Int64.Type}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"date", "Postion", "ID", "Index"}), #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Postion"}, {{"Rows", each _, type table [date=nullable date, Postion=nullable number, ID=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let AllList = Table.ToColumns([Rows]), ReturnDate = {null} & Table.RemoveLastN([Rows],1)[date], ReturnID = {null} & List.RemoveLastN(_[Rows][ID],1), CombineLists = AllList & {ReturnDate} & {ReturnID} , Columns = Table.ColumnNames([Rows]) in Table.FromColumns(CombineLists, Columns & {"Return Date", " Return ID"})), Expanded = Table.Combine(#"Added Custom"[Custom]), #"Changed Type1" = Table.TransformColumnTypes(Expanded,{{"Return Date", type date}, {" Return ID", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}}) in #"Sorted Rows"

Re: Custom Column please help!!!!

Mattym80 — Fri, 09 Sep 2022 16:30:06 GMT

Hi latimeria Anonymous Great stuff both examples were spot on, great having 2 options now one for dax and one for M. Thank you both for your support.