Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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...
DAX
PARTITION
ROWNUM
SQL
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks selimovd 

I have actually Succesfully created the Index column Measure now .....But now I want to use this measure to create a New Measure Marker Column ....expected result is something like this 

Report Date EDWCustomerID Index Marker
8/31/2020 0:00 3 1 0
9/30/2020 0:00 3 2 0
10/31/2020 0:00 3 3 0
11/30/2020 0:00 3 4 0
4/30/2021 0:00 3 5 1
8/31/2020 0:00 4 1 0
9/30/2020 0:00 4 2 0
10/31/2020 0:00 4 3 0
11/30/2020 0:00 4 4 0
4/30/2021 0:00 4 5 1
8/31/2020 0:00 5 1 0
9/30/2020 0:00 5 2 0
10/31/2020 0:00 5 3 0
11/30/2020 0:00 5 4 0
4/30/2021 0:00 5 5 1

 

Here I want to mark the row with max value in the Index column per Reporte Date per EDWCustomerid

Hopefully this makes sense. Thanks in advance.

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
5 years ago
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's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks for the resposne CNENFRNL 
    I need a DAX measure for this becasue my Dates change dynamically