Forum Discussion
Adding a simple index column using dax
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:
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.
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.
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?