Forum Discussion

clim2f88j's avatar
clim2f88j
Frequent Visitor
2 years ago

Index Column Based on ID and Date

Hello everyone,   I need to add an index column based on consumers ID and date.   Data: ID Date AAA 1/1/23 AAA 3/7/23 AAA 4/18/23 BBB 12/19/22 BBB 4/5/23 CCC 7/9/23 ...
Need Help
Anand24's avatar
Anand24
Super User
2 years ago

Hi clim2f88j ,
Create a calculated measure with below DAX:

Index =
RANKX (
    ALLEXCEPT('Table', 'Table'[ID]),
    CALCULATE ( MAX ( 'Table'[Date] ) ),
    ,
    ASC,
    DENSE
) - 1

Here's the result:

 

 

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

Proud To Be a Super User !!!
LinkedIn
Sammyben's avatar
Sammyben
New Member
2 years ago

I have same question but I need to create by id then category and then by date. Hiw can I add also the category?

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Sammyben, something like this? (if no provide sample data and expected result please)

     

    Result:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRcjYEEob6hvpGBkbGSrE6KOLG+uYY4kY4xEHqTfQNLRASTk5OcAuM9A0tQTJG6DIm+qYYOoxQxGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Category = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Date", type date}}, "en-US"),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID", "Category"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table}}),
    #"Combined Tables" = Table.Combine(#"Grouped Rows"[Data])
in
    #"Combined Tables"