Forum Discussion

Christianvs's avatar
Christianvs
Helper II
3 years ago
Solved

Sorting and adding columns

Hi

 

I am trying to add and index/rank row to a table and in order to not get duplicate index numbers I used the DAX writte by Greg_Deckler in this post https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Mythical-DAX-Index/m-p/1093214#M528.

 

It works perfectly, but I have three questions that I hope someone can help me with. First:

Is there a way to ensure that the column the sorting is based on ([Dage aktiv] in my particular case) is ordered in a descending order?

 

And the second question:

Can I change the name of the new index row within the function so that it is something else than [Value]?

 

And the last question:

Can I add another row from an already existing table in the function? I tried to add another within the ADDCOLUMN function by adding ,"Number",'DW CSRL'[Number], but that do not work.

 

 

 

Thanks

Need Help
  • HoangHugo's avatar
    HoangHugo
    3 years ago

    Hi

    Q1: GENERATESERIES will always return index column in Ascending (1 to ...), so you can create new column to RANKX your [Value] in descending.

    Q2: yes, you can change column name in your function like ADDCOLUMNS("your expected name",GENERATESERIES(..))
    Q3: Yes, you can. Can you show me detail of error?

3 Replies

  • HoangHugo's avatar
    HoangHugo
    Solution Specialist
    3 years ago

    Hi

    Q1: GENERATESERIES will always return index column in Ascending (1 to ...), so you can create new column to RANKX your [Value] in descending.

    Q2: yes, you can change column name in your function like ADDCOLUMNS("your expected name",GENERATESERIES(..))
    Q3: Yes, you can. Can you show me detail of error?

    • Christianvs's avatar
      Christianvs
      Helper II
      3 years ago

      Thanks for your replies HoangHugo and v-cgao-msft.

       

      I got it working now 🙂

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Christianvs ,

     

    //I am trying to add and index/rank row to a table and in order to not get duplicate index numbers
    Greg's method addresses the index issue for calculated tables. Is the table you need to get the index column for a calculated table? If not, you can easily create this index column in Power Query Editor.

     

    1. As per the above, the way I can think of is to create another calculated column, similar to:

    Index = CALCULATE(COUNTROWS('Indexed Table'),FILTER(ALL('Indexed Table'),'Indexed Table'[Value]>=EARLIER('Indexed Table'[Value])))

    2. You can rename the field [Value] after the new table is completed.

    3. This requires that each line return a scalar or unique value. If satisfied, you can use RELATED (with entity relationship) or LOOKUPVALUE (without relationship) to get the value.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data