Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
derekgould
Frequent Visitor

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 5
derekgould
Frequent Visitor

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.

 

NameDateCommentGenerated 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, @derekgould 

 

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

vzhangti_0-1702367709113.png

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

vzhangti_1-1702367896390.png

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.

 

 

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.

Hi, @derekgould 

 

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

 

Best Regards

v-zhangti
Community Support
Community Support

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors