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

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's avatar
ToddChitt
Super User
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?