cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hnguy71 Member
Member

Sequential / True Index Column

Hello All!
I've been looking at a few blog posts by @Greg_Deckler  namely these two:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231

https://community.powerbi.com/t5/Community-Blog/For-and-While-Loops-in-DAX/ba-p/636314

and I'm interested in something similar for my dataset but I'm not quite sure how to replicate it. I know that it's possible using RANKX to obtain an index but the results are not always foolproof.  Within my actual dataset, I can have repeats multiple times and I'll have a few ranks that share the same number even with tie breakers. I'd like to explore using GENERATESERIES to obtain a more precise method of creating sequential numbering. 

In my sample dataset:

GMPLSLTBDifferencePATH
GM3PL3316060316326664-1.3E+07Parent
GM3PL3316060316326664-1.3E+07Parent
GM2PL2320020016689238-1.3E+07Parent
GM1PL1372465618909476-1.5E+07Parent
GM2PL2320020016689238-1.3E+07Parent
GM1PL1372465618909476-1.5E+07Parent
GM3PL3951323159-13646Child
GM3PL3219434842-32648Child
GM3PL3676335962-29199Child
GM3PL3546022639-17179Child
GM3PL388143990-43109Child
GM3PL342297695-3466Child
GM3PL3527240833-35561Child
GM3PL3911345013-35900Child
GM3PL3135129230-27879Child
GM3PL3502211549-6527Child
GM3PL3659810987-4389Child
GM3PL3265722719-20062Child
GM3PL3801631436-23420Child
GM3PL316834725-3042Child
GM3PL3762146449-38828Child
GM3PL3738029524-22144Child
GM3PL3296814952-11984Child
GM3PL392941024-40095Child
GM3PL3574645525-39779Child
GM3PL3723238609-31377Child
GM3PL3778137020-29239Child
GM3PL3450942219-37710Child
GM3PL3303770-467Child
GM3PL3519041499-36309Child
GM3PL3188826833-24945Child
GM3PL3163818107-16469Child
GM3PL396819421-18453Child
GM3PL3866840059-31391Child
GM3PL3115315919-14766Child
GM3PL3819311360-3167Child
GM3PL317603087-1327Child
GM3PL338535366-1513Child
GM3PL37847949-47871Child
GM3PL3963922698-13059Child
GM3PL3889914817418Child
GM3PL3616118683-12522Child
GM3PL3981623755-13939Child
GM3PL3686442152649Child
GM3PL3975535393-25638Child
GM3PL350736094-35587Child
GM3PL384627861601Child
GM3PL3819738506-30309Child
GM3PL3554845251-39703Child
GM3PL357056267-562Child
GM3PL3240612106-9700Child
GM3PL3269325381-22688Child
GM3PL3195648115-46159Child
GM3PL3300236100-33098Child
GM3PL3661246062006Child
GM3PL3524711755-6508Child
GM3PL3483122902541Child
GM3PL3200336986-34983Child
GM3PL342706118-1848Child
GM3PL3833018053-9723Child
GM3PL3991149307-39396Child
GM3PL3500014462-9462Child
GM3PL3258537327-34742Child
GM3PL3480746230-41423Child
GM3PL3101933757-32738Child
GM3PL3848121375-12894Child
GM3PL3901722383-13366Child
GM3PL3754449743-42199Child
GM3PL3929214356-5064Child
GM3PL390916996-16087Child
GM3PL3844431738-23294Child
GM3PL3350541033-37528Child
GM3PL392711271-10344Child
GM3PL3389643486-39590Child
GM3PL311176570-5453Child
GM3PL3272615987-13261Child
GM3PL3924148799-39558Child
GM3PL3177820205-18427Child
GM3PL312946709-5415Child
GM3PL3986026015-16155Child
GM3PL3580328675-22872Child
GM3PL337108007-4297Child
GM3PL3971616400-6684Child
GM3PL3154246233-44691Child
GM3PL392919811-18882Child
GM3PL3811111533-3422Child
GM3PL3843148818-40387Child
GM3PL3468236613-31931Child
GM3PL3783710618-2781Child
GM3PL3591132341-26430Child
GM3PL3116713851-12684Child
GM3PL3465847782-43124Child
GM3PL3825315924-7671Child
GM3PL3914546691-37546Child
GM3PL3317613400-10224Child
GM3PL3340414766-11362Child
GM3PL3626444517-38253Child
GM3PL3201010250-8240Child
GM3PL3448838620-34132Child

I have 100 records and using this DAX formula in a calculated column I'm able to achieve close to all distinct values:

Sequential = RANKX(ALL('Sample'), [SL] + [TB] / 100000,,ASC,Dense)


My intended output is to have all 100 records to be distinct.
Any help with utilizing generateseries or creating a more precise sequential numbering is greatly appreciated!

4 REPLIES 4
Super User
Super User

Re: Seqential / True Index Column

Hello @hnguy71,

Is adding an index column in the query editor an option for you?  This would guarantee the uniqueness.

addindexcolumn.jpg

 

hnguy71 Member
Member

Re: Seqential / True Index Column

Hi @jdbuchanan71 ,
my current solution uses the PQ index function but unfortunately my criteria has changed recently which requires a different approach.

Community Support Team
Community Support Team

Re: Seqential / True Index Column

Hi @hnguy71 ,

You could refer to my reply of this similar thread which should be helpful.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hnguy71 Member
Member

Re: Seqential / True Index Column

@v-piga-msft  wow that's nifty! Unfortunately it gives me similar results as the RANKX measure that I'm using. I have duplicate records in my dataset and each record should increment individually. The results I have currently is this:
duplicates.png

I'm expecting:

PATHGMPLTBSLDifferenceSampleMeasure
ParentGM1PL1189094763724656-150000001
ParentGM2PL2166892383200200-130000002
ParentGM1PL1189094763724656-150000003
ParentGM2PL2166892383200200-130000004
ParentGM3PL3163266643160603-130000005
ParentGM3PL3163266643160603-130000006

 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 341 members 3,372 guests
Please welcome our newest community members: