Forum Discussion

Marlonrb's avatar
Marlonrb
Frequent Visitor
10 years ago

Custom column Index or Ranking by other column

Hi Team!!! I have a table with AccountReceived informations and Clients.   I need to create a new custom column (like an Index) that define an order according the client column changed.   I mean...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

I would create another table with just the customers and put an index column on it. Then you should be able to relate the two tables and use the "Sort by" functionality or just filter your visual/page/report to that ID field <= 20.

 

In Query Editor, go to Add Column and then use the Add Index Column drop down in the General area of the ribbon (1st box).

  • Marlonrb's avatar
    Marlonrb
    Frequent Visitor
    10 years ago

    I used the same select, because we've half milion records in our client table.

     

  • Marlonrb's avatar
    Marlonrb
    Frequent Visitor
    10 years ago

    Hi my friend,  

    Can I bring to P.BI, my client table even it has half milion of records?

     

    or How can I create a RANK measure in my table? I need to rank and show only all my 20 big clients

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      10 years ago

      Sure, half a million records should not be too much of a problem, I've created datasets with millions of rows in them.

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee
      10 years ago

      Marlonrb,

       

      According to your description, you want to create a rank column which only increases when group changes. Right?

       

      In this scenario, you can just use RANKX() to dense rank based on the group column, please refer to my sample below:

       

      rank = RANKX(Query1,Query1[Table_DAX_sorting[name]]],,ASC,Dense)

       

      Regards,

  • Ying_P's avatar
    Ying_P
    Helper I
    9 years ago

    Hi,

     

    I am super new to Power BI and looking for the information about how to add an index column on a table created in Power BI.

     

     

    Could you explain this with a bit more details? Thanks a lot!

     

    • Marlonrb's avatar
      Marlonrb
      Frequent Visitor
      9 years ago

      Ying_P

      In the Query Editor you can create a different type of index column.

      • Ying_P's avatar
        Ying_P
        Helper I
        9 years ago

        Thank you Marlonrb! The problem is: because the table was created in Power BI (data view), after opening edit queries, I was not able to see it in the "Queries" list, so I couldn't select and edit it. Do you know what I make the table show up?

         

        Thanks a lot!