Adding a simple index column using dax

[samyak07]

Hi,

I just want to add an index column to a calculated table in Power BI. Every solution that I have seen uses RANKX to compare rows & assign a rank to them. But, in my case I have duplicate rows too & I do not care about their rank. I just need a unique serial number column which I can use for cumulative sum.

The input table looks something like this. I need to add a Sr no. column without considering any rank or logic.

Product	Move-Type	Pieces	Sr no.
4343	MM	2	1
3243	Flex	5	2
54543	NM	2	3
2431	fLEX	2	4

Thanks in advance for your help

Regards,

Samyak

[samyak07]

Also my visual would be a table showing relevant columns. The end goal is to partition this material list & assign each to specific dates. So materials with cumulative sum(CS)<500 would be assigned to a specific order date, materials with 500<CS<=1000 to another date... & so on

[ToddChitt · Accepted solution]

So if I understand you correctly, the rows marked "Must Move" should be ranked as more important (i.e. lower index) than "Flexible", with "Not Move" being less important. 

If you have this Column already:

Foo = "Foo"

And this column:

MyPriority = IF ( [Move priority] = "Must Move", 1, IF ( [Move priority] = "Flexible", 2, 3))

Then you can add this column:

My Rank = RANK(DENSE,,ORDERBY('Table'[MyPriority], ASC, 'Table'[MATNR], ASC),DEFAULT,PARTITIONBY('Table'[Foo]),)

Result:

[samyak07]

Hi,

This solution worked wonderfully. I was able to assign unqiue serial numbers to all the rows. Thanks a lot!
Can I also trouble you with a follow-up questions. Now, when I try to calculate the cumulative sum using the formula below, it gives me the sum of all pieces (16) in all rows

Pieces_cumulative = CALCULATE(SUM('TABLE'[pieces]), FILTER( ALL('table'),'TABLE'[Serial Number]<=MAX('TABLE'[Serial Number])))
Could you please point out what am I doing wrong here?
Thanks in advance!

[ToddChitt]

If the solution worked, accepting it as such lets other know where to find answers. Plus it really makes me feel good and I like to feel good. 🙂

For your cumulative sum issue, try this post:

Solved: Cumulative sum in DAX - Microsoft Fabric Community

Your DAX looks right, Can you build a simple table visual with the following columns: [Serial Number], [pieces] ,[cumulative DAX measure].

[samyak07]

Hi,

Sorry for that. I have accepted the solution!

Also, I notice that the cumulative sum formula I proposed, works if I use it as a measure instead of a calculated column. However, I would like to have the sums in a calculated column as I have to use it for further analysis. Additionally using measure for cumulative sum makes my report slower as the measure has to be evaluated for 60,000+ rows for every change I make in the tabular visual.
Kindly help me to create a cumulative sum calculated column?

[ToddChitt]

We are moving on to a new topic, which really should be a new post, but I think this might work:

Create a DAX table using the SELECTED COLUMNS function. In that, pick the columns you need, PLUS the measure in question, and the result is a table where that measure is represented as a column.

It will still be slow as Power BI needs to evaluate 1) the Index column, then 2) the measure, then 3) the DAX table.

[samyak07]

Sure,

I can give you a rough idea of how my calculated table looks like:

Now my final goal is to calculate the 'Cumulative sum of pieces' column. As my calculated table doesn't have any order-date or index column, I need to add one for the cumulative sum. Thus, I want to order the 'Serial Number' column.
Also I need to assign the serial number based on the values in 'Move priority' column. So all 'Move Priority'="Must Move" should be numbered first as 1,2.. & then 'Move Priority' ="Flexible" as 3,4,5 & finally 'Move Priority ="Not move" as 6. These serial numbers would be used for calculating the cumulative sum in same order.

It would be really great if you can propose a simpler solution here?

My solution was to just sort based on categories, all material belonging to "Must move" appear first & then flexible & then "Not Move". I would then just have to add an index column with unique serial numbers. But thats quite challenging too. I don't think adding enough columns to the table to make rows unique would make much sense because my original dataset has 60,000+ rows & the data changes on a daily basis.

[ToddChitt]

You should still be able to use the RANK or RANKX funtion for this. The trick is to use the right PARTITION inside the function. Say you wanted to rank your users by Sales for each State. The ORDER BY clause would be Sales, and the PARTITION BY would be State. 

I suggest you add a static column as: MyStaticColumn = "Foo"

Then reference that column in the RANK function's PARTITION parameter. That puts ALL RECORDS in the table in the same partition, generating only one series of numbers starting at 1. 

It is up to you to figure out the ORDER BY portion.

[samyak07]

Hi,

I got your idea of identifying a right partition by adding a dummy column with Foo. However by using RANKX, I still have to rank it based on 'Sales' value. Now, if there are duplicate sales values, rankx assigns same rank to them. Thus, I do not have the required unique serial numbers. 
So to better phrase my query, I want to assign unique serial numbers even if there are whole duplicate rows or values in 'Sales' column. As my requirement is not to rank rows but to calculate cumulative sum, I need an index column.

[ToddChitt]

The trick is to add enough columns in the ORDER BY portion to guarantee that you have no duplicates. 

I think the bigger question is this: What are you trying to accomplish in the end? What type of visual are you trying to present to your users? What story are you trying to tell with your data?