Forum Discussion

Harineeeeeeee's avatar
Harineeeeeeee
Helper I
2 years ago
Solved

How to create index on visual table

Hey everyone
I have created a table visual with multiple colums from different table after creating the visual I would like to create a index column in the table visual how to do this ? Any leads here please?
I have tried using the below DAX that didn’t work that gave up same numbers in all rows,
Rankx(allselected(main table),calculate(max(maintable[id])),asc,dense)

So my question is how to generate incremental row values on the visual table

Need Help
  • DallasBaba's avatar
    DallasBaba
    2 years ago

    Harineeeeeeee The index column is not displaying unique values because of the  ALLSELECTED function.

    You can try using ALL('MainTable') instead of ALLSELECTED('MainTable')

    IndexColumn = RANKX(ALL('MainTable'), 'MainTable'[id], , ASC, Dense)

    Or 

    Index = COUNTROWS(FILTER(ALL(MainTable), MainTable[id] < EARLIER(MainTable[id]))) + 1

     

    I hope this meets your needs. If so, please consider Accepting it as the solution to help others find it more quickly. OR
    If my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

     
    Best Regards,
    Dallas.

     

     

7 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    Hello Harineeeeeeee ,

     

    are you able to show the example and the output that you would want to achieve ?  that would help alot 

     

    best regards,

    • Harineeeeeeee's avatar
      Harineeeeeeee
      Helper I
      2 years ago

      Considering yellow table is from table A and orange is from table b , after i create a visual combining table a and table b , i want to create a index number 
      Can you please suggest any ways?

  • DallasBaba's avatar
    DallasBaba
    Skilled Sharer
    2 years ago

    Harineeeeeeee your approach is right.

    You can modify the query using the following formulas  

    IndexColumn = RANKX(ALLSELECTED('MainTable'), 'MainTable'[id], , ASC, Dense)

     

    or

    Index = COUNTROWS(FILTER(ALLSELECTED(MainTable), MainTable[id] <= EARLIER(MainTable[id])))

     

    Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
    Note: 

    If this post helps, please consider Accepting it as the solution to help others find it more quickly. OR
    If my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
     
    Best Regards,
    Dallas.
    • Harineeeeeeee's avatar
      Harineeeeeeee
      Helper I
      2 years ago

      DallasBaba 

       

      Thanks for the below, now the values are not unique but it gives me some random number not realy sure on what basis that random number is coming, even though i used maintabl[id] as index column which starts from 0.

       

      Any thoughts on this?

       

      Thanks

      • DallasBaba's avatar
        DallasBaba
        Skilled Sharer
        2 years ago

        Harineeeeeeee The index column is not displaying unique values because of the  ALLSELECTED function.

        You can try using ALL('MainTable') instead of ALLSELECTED('MainTable')

        IndexColumn = RANKX(ALL('MainTable'), 'MainTable'[id], , ASC, Dense)

        Or 

        Index = COUNTROWS(FILTER(ALL(MainTable), MainTable[id] < EARLIER(MainTable[id]))) + 1

         

        I hope this meets your needs. If so, please consider Accepting it as the solution to help others find it more quickly. OR
        If my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

         
        Best Regards,
        Dallas.

         

         

  • DallasBaba's avatar
    DallasBaba
    Skilled Sharer
    2 years ago

    Harineeeeeeee Happy to know it works.  Kindly @ me with any future needs around Power BI and Fabric.

    Please give it a kudos by clicking the Thumbs Up! It goes a long way to helping others find this solution quickly.

     
    Best Regards,
    Dallas.