index per group

[Anonymous]

Hi,

I have following table

ID	date	item no.
aaa	2020/1/1	1
aaa	2020/1/1	2
aaa	2020/2/10	30
aaa	2020/2/10	6
bbb	2020/2/17	1
bbb	2020/2/17	30
bbb	2020/2/18	2

and i want an index with following conditions:

1) only item no. 30

2) the index needs to be in the same order than date

ID	item	index
aaa	30	2 (ordered second time)
bbb	30	1 (ordered first time)

Please advise, thank you

[Fowmy]

@Anonymous 

Add this as a new calculated column in your model:

Index = 
VAR _ID = [ID]
VAR _RANK = 
RANKX(
    FILTER(IndexTable,IndexTable[ID] = _ID),
    IndexTable[date],,ASC,Dense
)
RETURN

IF( IndexTable[item no.] = 30, _RANK,BLANK())

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Fowmy]

@Anonymous 

What do mean by "ordered second time" and "ordered first time"?
can you explain?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

@Fowmy 

For ID aaa:

1 (first time) 2020/1/1 but no item no. 30

2 (second time) 2020/2/10  and bought item no. 30

So the index should show, ID (customer)  aaa bought item no. 30 on the 2 (second time)

aaa

30

2

 ID bbb, bought item no.30 on 2020/2/17, this was the first time (1) item no. 30 was ordered from ID bbb.

The second time ID bbb bought something, item no. 30 was not included, so we can ignore this.

bbb

30

1

[Icey · Accepted solution]

Hi @Anonymous ,

Please check:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxMVNJRMjIwMtA31DcEMhUMlWJ1sIkboYsb6RsagCSMDXDJmIElkpKSkCTMEXZgkYCahSpjAZIB2R8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, date = _t, #"item no." = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"date", type date}, {"item no.", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "date"}, {{"Count", each _, type table [ID=nullable text, date=nullable date, #"item no."=nullable number]}}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns", {"ID"}, {{"Count", each _, type table [ID=nullable text, date=nullable date, Count=table]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([Count],"Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"date", "Count", "Index"}, {"date", "Count.1", "Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Expanded Count.1" = Table.ExpandTableColumn(#"Removed Columns1", "Count.1", {"item no."}, {"item no."}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Count.1", each ([#"item no."] = 30))
in
    #"Filtered Rows"

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Icey thanks, that was exactly was I was looking for.

[Fowmy]

@Anonymous 

Did you try my solution?

Appreceate your feedback and marking as a another possible solution.  

Thanks