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
selimovd's avatar
selimovd
Most Valuable Professional
5 years ago

Hey Anonymous ,

 

it's not possible to nest FILTER functions. Just combine in one filter what you want to filter.

I guess you wanted something like this:

Index =
CALCULATE(
    COUNTROWS( 'Member Channel Utilization' ),
    FILTER(
        ALLSELECTED( 'Member Channel Utilization' ),
        'Member Channel Utilization'[Report Date] <= EARLIER( 'Member Channel Utilization'[Report Date] ) && 'Member Channel Utilization'[EDWCustomerID] = EARLIER( 'Member Channel Utilization'[EDWCustomerID] )
    )
)

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
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
    Community 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