Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
GM | PL | SL | TB | Difference | PATH |
GM3 | PL3 | 3160603 | 16326664 | -1.3E+07 | Parent |
GM3 | PL3 | 3160603 | 16326664 | -1.3E+07 | Parent |
GM2 | PL2 | 3200200 | 16689238 | -1.3E+07 | Parent |
GM1 | PL1 | 3724656 | 18909476 | -1.5E+07 | Parent |
GM2 | PL2 | 3200200 | 16689238 | -1.3E+07 | Parent |
GM1 | PL1 | 3724656 | 18909476 | -1.5E+07 | Parent |
GM3 | PL3 | 9513 | 23159 | -13646 | Child |
GM3 | PL3 | 2194 | 34842 | -32648 | Child |
GM3 | PL3 | 6763 | 35962 | -29199 | Child |
GM3 | PL3 | 5460 | 22639 | -17179 | Child |
GM3 | PL3 | 881 | 43990 | -43109 | Child |
GM3 | PL3 | 4229 | 7695 | -3466 | Child |
GM3 | PL3 | 5272 | 40833 | -35561 | Child |
GM3 | PL3 | 9113 | 45013 | -35900 | Child |
GM3 | PL3 | 1351 | 29230 | -27879 | Child |
GM3 | PL3 | 5022 | 11549 | -6527 | Child |
GM3 | PL3 | 6598 | 10987 | -4389 | Child |
GM3 | PL3 | 2657 | 22719 | -20062 | Child |
GM3 | PL3 | 8016 | 31436 | -23420 | Child |
GM3 | PL3 | 1683 | 4725 | -3042 | Child |
GM3 | PL3 | 7621 | 46449 | -38828 | Child |
GM3 | PL3 | 7380 | 29524 | -22144 | Child |
GM3 | PL3 | 2968 | 14952 | -11984 | Child |
GM3 | PL3 | 929 | 41024 | -40095 | Child |
GM3 | PL3 | 5746 | 45525 | -39779 | Child |
GM3 | PL3 | 7232 | 38609 | -31377 | Child |
GM3 | PL3 | 7781 | 37020 | -29239 | Child |
GM3 | PL3 | 4509 | 42219 | -37710 | Child |
GM3 | PL3 | 303 | 770 | -467 | Child |
GM3 | PL3 | 5190 | 41499 | -36309 | Child |
GM3 | PL3 | 1888 | 26833 | -24945 | Child |
GM3 | PL3 | 1638 | 18107 | -16469 | Child |
GM3 | PL3 | 968 | 19421 | -18453 | Child |
GM3 | PL3 | 8668 | 40059 | -31391 | Child |
GM3 | PL3 | 1153 | 15919 | -14766 | Child |
GM3 | PL3 | 8193 | 11360 | -3167 | Child |
GM3 | PL3 | 1760 | 3087 | -1327 | Child |
GM3 | PL3 | 3853 | 5366 | -1513 | Child |
GM3 | PL3 | 78 | 47949 | -47871 | Child |
GM3 | PL3 | 9639 | 22698 | -13059 | Child |
GM3 | PL3 | 8899 | 1481 | 7418 | Child |
GM3 | PL3 | 6161 | 18683 | -12522 | Child |
GM3 | PL3 | 9816 | 23755 | -13939 | Child |
GM3 | PL3 | 6864 | 4215 | 2649 | Child |
GM3 | PL3 | 9755 | 35393 | -25638 | Child |
GM3 | PL3 | 507 | 36094 | -35587 | Child |
GM3 | PL3 | 8462 | 7861 | 601 | Child |
GM3 | PL3 | 8197 | 38506 | -30309 | Child |
GM3 | PL3 | 5548 | 45251 | -39703 | Child |
GM3 | PL3 | 5705 | 6267 | -562 | Child |
GM3 | PL3 | 2406 | 12106 | -9700 | Child |
GM3 | PL3 | 2693 | 25381 | -22688 | Child |
GM3 | PL3 | 1956 | 48115 | -46159 | Child |
GM3 | PL3 | 3002 | 36100 | -33098 | Child |
GM3 | PL3 | 6612 | 4606 | 2006 | Child |
GM3 | PL3 | 5247 | 11755 | -6508 | Child |
GM3 | PL3 | 4831 | 2290 | 2541 | Child |
GM3 | PL3 | 2003 | 36986 | -34983 | Child |
GM3 | PL3 | 4270 | 6118 | -1848 | Child |
GM3 | PL3 | 8330 | 18053 | -9723 | Child |
GM3 | PL3 | 9911 | 49307 | -39396 | Child |
GM3 | PL3 | 5000 | 14462 | -9462 | Child |
GM3 | PL3 | 2585 | 37327 | -34742 | Child |
GM3 | PL3 | 4807 | 46230 | -41423 | Child |
GM3 | PL3 | 1019 | 33757 | -32738 | Child |
GM3 | PL3 | 8481 | 21375 | -12894 | Child |
GM3 | PL3 | 9017 | 22383 | -13366 | Child |
GM3 | PL3 | 7544 | 49743 | -42199 | Child |
GM3 | PL3 | 9292 | 14356 | -5064 | Child |
GM3 | PL3 | 909 | 16996 | -16087 | Child |
GM3 | PL3 | 8444 | 31738 | -23294 | Child |
GM3 | PL3 | 3505 | 41033 | -37528 | Child |
GM3 | PL3 | 927 | 11271 | -10344 | Child |
GM3 | PL3 | 3896 | 43486 | -39590 | Child |
GM3 | PL3 | 1117 | 6570 | -5453 | Child |
GM3 | PL3 | 2726 | 15987 | -13261 | Child |
GM3 | PL3 | 9241 | 48799 | -39558 | Child |
GM3 | PL3 | 1778 | 20205 | -18427 | Child |
GM3 | PL3 | 1294 | 6709 | -5415 | Child |
GM3 | PL3 | 9860 | 26015 | -16155 | Child |
GM3 | PL3 | 5803 | 28675 | -22872 | Child |
GM3 | PL3 | 3710 | 8007 | -4297 | Child |
GM3 | PL3 | 9716 | 16400 | -6684 | Child |
GM3 | PL3 | 1542 | 46233 | -44691 | Child |
GM3 | PL3 | 929 | 19811 | -18882 | Child |
GM3 | PL3 | 8111 | 11533 | -3422 | Child |
GM3 | PL3 | 8431 | 48818 | -40387 | Child |
GM3 | PL3 | 4682 | 36613 | -31931 | Child |
GM3 | PL3 | 7837 | 10618 | -2781 | Child |
GM3 | PL3 | 5911 | 32341 | -26430 | Child |
GM3 | PL3 | 1167 | 13851 | -12684 | Child |
GM3 | PL3 | 4658 | 47782 | -43124 | Child |
GM3 | PL3 | 8253 | 15924 | -7671 | Child |
GM3 | PL3 | 9145 | 46691 | -37546 | Child |
GM3 | PL3 | 3176 | 13400 | -10224 | Child |
GM3 | PL3 | 3404 | 14766 | -11362 | Child |
GM3 | PL3 | 6264 | 44517 | -38253 | Child |
GM3 | PL3 | 2010 | 10250 | -8240 | Child |
GM3 | PL3 | 4488 | 38620 | -34132 | Child |
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!
Hello @hnguy71,
Is adding an index column in the query editor an option for you? This would guarantee the uniqueness.
Hi @jdbuchanan71 ,
my current solution uses the PQ index function but unfortunately my criteria has changed recently which requires a different approach.
Hi @hnguy71 ,
You could refer to my reply of this similar thread which should be helpful.
Best Regards,
Cherry
@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:
I'm expecting:
PATH | GM | PL | TB | SL | Difference | SampleMeasure |
Parent | GM1 | PL1 | 18909476 | 3724656 | -15000000 | 1 |
Parent | GM2 | PL2 | 16689238 | 3200200 | -13000000 | 2 |
Parent | GM1 | PL1 | 18909476 | 3724656 | -15000000 | 3 |
Parent | GM2 | PL2 | 16689238 | 3200200 | -13000000 | 4 |
Parent | GM3 | PL3 | 16326664 | 3160603 | -13000000 | 5 |
Parent | GM3 | PL3 | 16326664 | 3160603 | -13000000 | 6 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |