Forum Discussion

Paulshap's avatar
Paulshap
Frequent Visitor
5 years ago
Solved

Rank the duplicates values

Hi community,
have the next issue. I have several values I have to rank.

 

The problem is that  ranks function assign the same rank to each row and there is no any identifier to break the tie. I can't create index in Powerquery as this is calculated table. I tried to generate index via GENERATESERIES in var and return this index, but it displayes the error.

http://prntscr.com/x9zipf 

 

The formula is below

Column 2 =
var gen=GENERATESERIES(1,COUNTROWS(Table),1)
return
SELECTCOLUMNS(gen,"index",[Value])
Does anybody have the same issue and how did you solve this problem?
Thanks in advance.
  • AlB's avatar
    AlB
    5 years ago

    Hi Paulshap 

    You could create a new calculated table:

     

    New table =
GENERATE (
    DISTINCT ( Table1 ),
    GENERATESERIES ( 1, CALCULATE ( COUNT ( Table1[sale_contract_id] ) ) )
)

     

    Otherwise perhaps you can create the equivalent of the calculated table in PQ and add an index afterwards.

    I don't think it is possible to add a column like that directly in DAX (witout creating a new table) since there is nothing to differentiate the duplicates. In any case, what is the point of this? What are you trying to do, ultimately? Most likely it can be done without that column, since it doesn't seem to add any information.

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

7 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Paulshap 

    You could create a new calculated table:

     

    New table =
GENERATE (
    DISTINCT ( Table1 ),
    GENERATESERIES ( 1, CALCULATE ( COUNT ( Table1[sale_contract_id] ) ) )
)

     

    Otherwise perhaps you can create the equivalent of the calculated table in PQ and add an index afterwards.

    I don't think it is possible to add a column like that directly in DAX (witout creating a new table) since there is nothing to differentiate the duplicates. In any case, what is the point of this? What are you trying to do, ultimately? Most likely it can be done without that column, since it doesn't seem to add any information.

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • Paulshap's avatar
      Paulshap
      Frequent Visitor
      5 years ago

      AlB thanks a lot. It works!😁 Regarding the aim - I had the parent table with unique agreement where it was displayed period of agreement( e.g. 3 months ),amount by agreement and % by this amount. I have to show the % proportionately the period (3 mon) with displaying date in  frame of start date and end date of agreement. I multiplied the agreement and need some index to increment the dates in frame  of agreement. The final result should look like http://prntscr.com/xagiki

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello Paulshap ,
    You can add the index column in the calculated table calculation instead of creating a column.

    VAR __Count = COUNTROWS('Table 1')
    VAR __Table =
    GENERATESERIES(1,__Count,1)
    RETURN
    __Table
    • Paulshap's avatar
      Paulshap
      Frequent Visitor
      5 years ago

      Hi, thanks for your solution, but I have to get single column with index, not total table. Your solution return total table with index.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hello Paulshap ,
        As you have said the table in the image is a calculated table.
        Add another column of index column by doing the calculation in that table calculation. I hope you're getting my point.