Rank the duplicates values

[Paulshap]

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 · Accepted solution]

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]

@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]

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]

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]

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.

[Paulshap]

Hi @Anonymous, if you mean your formula, it give the result of total table, not single column. Thanks.

[Anonymous]

Hello @Paulshap , 
Can you share your sample data or file after removing the sensitive data?