DAX: Create and auto fill a column with numerical sequence
Question
Friday, May 13, 2016 9:19 PM
Hi all,
How do I create/auto fill a numerical sequence column (like in Excel) using DAX when I don't have the data? Meaning, I want to create the [ID] column below in my table ('Names'). All I have now is the [Name] column.
Is there a formula to create the [ID] column without creating a Linked Table?
| Name |
| John |
| Mark |
| Tom |
| Ken |
Thanks,
~UG
All replies (5)
Friday, May 13, 2016 11:10 PM âś…Answered
You can "kind of" do this, but the bigger question here is why? Off the top of my head I can't think of a good reason for generating a sequence number inside the model. What are you planning to do with this number? There may be an alternate approach to whatever it is you are trying to do that will be more efficient.
You could use something like the following
=CALCULATE(COUNTROWS(),Filter(values(Table1[Name]), EARLIER(Table1[Name]) >= Table1[Name]))
But this does drive off the distinct values in the name column and gives them an sequence based on their alphabetic order. So it won't be 1,2,3,4 it would return 1,3,4,2. And if you ever get any duplicate values they will get the same sequence number.
http://darren.gosbell.com - please mark correct answers
Saturday, May 14, 2016 12:54 AM
Short version, I have bridge table due to a many-to-many issue...that ended up needing yet another way to provide me unique values because of additional duplicates. So this won't work, unfortunately.
Appreciate your efforts to help though! :-)
Saturday, May 14, 2016 4:36 AM
That actually sounds like a data problem that should be fixed in the source table, not worked around in your tabular model.
http://darren.gosbell.com - please mark correct answers
Saturday, May 14, 2016 4:54 AM
Certainly understand that it sounds like it without the details, but it's not. Data is absolutely pure. I QA'd it with other means.
Just looking for a creative shortcut that mimicked Excel's auto-fill feature, per above. Absolutely no worries. Thought I'd at least post here first for some ideas, because this board is unbelievably helpful. Thanks again, Darren!! You rock.
Mods - I'm still marking Darren's answer as sufficient, only assuming his parameters. It will certainly help others, and is very good for other folks to use as needed.
~UG
Saturday, May 14, 2016 7:40 PM
Ditto Darren on all points.
Sequence can be added with Power Query (aka Get & Transform)
with Index().