Forum Discussion
How to dynamically number UserID and PurchaseDate
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.
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:
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.
5 Replies
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.
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])))
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.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:
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.
