Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Adding a simple index column using dax

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...
Need Help
  • ToddChitt's avatar
    ToddChitt
    3 years ago

    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:
     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
ToddChitt
Super User
3 years ago

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].

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      ToddChitt
      Super User
      3 years ago

      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.