topic Renumbering data items dynamically in DAX Commands and Tips

Renumbering data items dynamically

Anonymous — Fri, 08 Dec 2023 13:23:47 GMT

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?

Re: Renumbering data items dynamically

v-zhangti — Mon, 11 Dec 2023 02:49:08 GMT

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.

Re: Renumbering data items dynamically

Anonymous — Mon, 11 Dec 2023 11:37:25 GMT

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?

Re: Renumbering data items dynamically

v-zhangti — Tue, 12 Dec 2023 07:59:11 GMT

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&"."&_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.

Re: Renumbering data items dynamically

Anonymous — Thu, 14 Dec 2023 18:48:48 GMT

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.

Re: Renumbering data items dynamically

v-zhangti — Fri, 15 Dec 2023 08:32:28 GMT

Hi, Anonymous

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

Best Regards