Forum Discussion
Renumbering data items dynamically
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?
5 Replies
Hi, Anonymous
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.
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;
- People (name) in the result set are numbered sequentially from 1 onwards
- 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?
Hi, Anonymous
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&"."&_N2Is 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.
