Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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.
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"
Hi @latimeria @v-yetao1-msft 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.
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"
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.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |