Renumbering data items dynamically

[derekgould]

We have a data set that includes communications. User would like to add an ID that shows the people numbered sequentially, followed by the sequence of the communication specific to that person.

For example, John Doe is first in the list, so he becomes #1, and his first communication has id 1.1, second is 1.2, etc. Jane Smith shows up, her first communication is 2.1, 2.2, etc. The data might not be ordered by person (ie could be John, Jane, John, George, Jane, etc) so the generation of the ID needs to take that into account.

If the data set is filtered or sorted, the numbers should be regenerated so that they are in the right sequence.

Can anyone suggest a straightforward way to do this in dax?

[derekgould]

Thanks for the response. Here's a simplified example of how the data will appear, and what I'd like to generate as the ID. Basically the algorithm is as follows;

1. People (name) in the result set are numbered sequentially from 1 onwards
2. Communications (Comment) are numbered sequentially for the person making the comment

So the Generated ID is the value I want to generate dynamically, but it has to be able to be regenerated  if the result set is filtered, sorted, slicer applied, etc.

Name	Date	Comment	Generated ID
John Doe	11-Dec	Some comment	1.1
Jane Smith	11-Dec	Some comment	2.1
John Doe	11-Dec	Some comment	1.2
John Doe	11-Dec	Some comment	1.3
Jane Smith	11-Dec	Some comment	2.2
Fred Flintstone	11-Dec	Some comment	3.1

Does that make it clearer? 

[v-zhangti]

Hi, @derekgould 

First you need to add an index column to the power query.

Measure:

Minindex = CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Name]))

Measure = 
Var _N1=RANKX(ALL('Table'),[Minindex],,ASC,Dense)
Var _N2=CALCULATE(COUNT('Table'[Name]),FILTER(ALL('Table'),[Name]=SELECTEDVALUE('Table'[Name])&&[Index]<=SELECTEDVALUE('Table'[Index])))
Return
_N1&"."&_N2

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

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

[derekgould]

This *is* the type of result I'm looking for, and I've managed to get it working with my actual data set, but is there a way to have it update dynamically if sorting or filtering is applied? For example if I set a date range, I'd like the numbering to start from 1 within that range.

[v-zhangti]

Hi, @derekgould 

Can you provide more example data? And give an example of the output you would expect.

Best Regards

[v-zhangti]

Hi, @derekgould 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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