Forum Discussion
Anonymous
5 years agoNot applicable
SQL query to DAX Measure conversion
I have the following SQL query that I have converted to DAX Measure SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY EOMDate DESC) FROM [TransArchive].dbo.AGGR_Transaction_Chann...
CNENFRNL
5 years agoCommunity Champion
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9LCsAgDETvkrWQxDFgzyLe/xottUhwun3MdwxxU7hWqyZFmsxyICzkCjtUGcWL2kd8+zJZtouCemoL0gRP4rKgIFDQzxGQ7Vk0bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Report Date" = _t, EDWCustomerID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Report Date", type date}, {"EDWCustomerID", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"EDWCustomerID"}, {{"ar", each let t = Table.AddIndexColumn(Table.Sort(_, {"Report Date", Order.Ascending}),"Index",1,1), max = List.Max(t[Index]) in Table.AddColumn(t, "Marker", each if [Index]=max then 1 else 0)}}),
#"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"Report Date", "Index", "Marker"}, {"Report Date", "Index", "Marker"})
in
#"Expanded ar"
Anonymous
5 years agoNot applicable
Thanks for the resposne CNENFRNL
I need a DAX measure for this becasue my Dates change dynamically