Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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_ChannelUtilization
WHERE EOMDate BETWEEN @dteEOMBegin AND @dteEOMEnd
DAX Measure :::
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] )
)
)
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.
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks for the resposne @CNENFRNL
I need a DAX measure for this becasue my Dates change dynamically
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
20 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
22 |