How to dynamically number UserID and PurchaseDate

[Smitu30]

Hello.

Please see the below sample table.

UserID	PurchaseDate
000001	2020-07-01
000001	2020-07-12
000001	2020-08-05
000001	2020-08-21
000002	2020-07-09
000002	2020-08-06
000002	2020-08-15
000002	2020-08-25

First, we add the row of PurchaseDateNo. to the table and number each UserID and PurchaseDate as below.

UserID	PurchaseDate	PurchaseDateNo.
000001	2020-07-01	1
000001	2020-07-12	2
000001	2020-08-05	3
000001	2020-08-21	4
000002	2020-07-09	1
000002	2020-08-06	2
000002	2020-08-15	3
000002	2020-08-25	4

Then, if we filter the line of July dates from each UserID and leave August dates as it is,
we also like to renumber each UserID and PurchaseDate like as below.

UserID	PurchaseDate	PurchaseDateNo.
000001	2020-08-05	1
000001	2020-08-21	2
000002	2020-08-06	1
000002	2020-08-15	2
000002	2020-08-25	3

However, we could not renumber correctly after changing the PurchaseDate.
Your advise and any solution would be highly appreciated.

Thanks.

[amitchandak]

@Smitu30 , Try a measure like

calculate(distinctcount(Table[PurchaseDate]), filter( allselected(Table),Table[UserID] =max(Table[UserID]) && Table[PurchaseDate] <=max(Table[PurchaseDate])))

or

calculate(count(Table[PurchaseDate]), filter( allselected(Table),Table[UserID] =max(Table[UserID]) && Table[PurchaseDate] <=max(Table[PurchaseDate])))

[Smitu30]

Hello.

Thank you very much for your advice.

I could number successfully after trying the measure.

Could you let me ask you one more thing about DAX as actually I am a beginner in it.

How did it come about that you put the below 2 conditions to FILTER function in this case.

「Table[UserID]=max(Table[UserID])」

「Table[PurchaseDate]<=max(Table[PurchaseDate])」

Appreciate your further cooperation in advance.

[Anonymous · Accepted solution]

Hi @Smitu30 

If you want to have a dynamic sort, you need to build a measure and use Rankx function. 

I build a table like yours to have a test.

Measure:

Measure = RANKX(FILTER(ALLSELECTED('Table'),'Table'[UserID]=MAX('Table'[UserID])),CALCULATE(MAX('Table'[PurchaseDate])),,ASC)

Result is as below.

Default:

Filter Date>=2020/8/1:

The max function in filter will return to a value = value in current row. So your filter will return to a table which values are all equal to current row.

Ex: Filter(Table,'Table'[UserID]=MAX('Table'[UserID])), IF current row's UserID = 1,so it will return to the table as below and calculate the rank of 'Table'[PurchaseDate] in this table. And you can refer to the blog as below to learn more about context filter and row filter.

For more info:

Filter 

row-context-and-filter-context-in-dax

You can download the pbix file from this link: How to dynamically number UserID and PurchaseDate

Best Regards,

Rico Zhou

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

[Smitu30]

Hello.

Now, I am all clear.

Many thanks for your kind advice and explanation.

[Greg_Deckler]

@Smitu30 Can you explain the logic of the renumbering to me? You have 2 1's. But, overall, seems like you need a RANX measure. 

https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

Ranking date.