topic Re: Rank the duplicates values in DAX Commands and Tips

Rank the duplicates values

Paulshap — Wed, 20 Jan 2021 09:25:58 GMT

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.

Re: Rank the duplicates values

Anonymous — Wed, 20 Jan 2021 10:11:51 GMT

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

Re: Rank the duplicates values

Paulshap — Wed, 20 Jan 2021 10:32:35 GMT

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

Re: Rank the duplicates values

Anonymous — Wed, 20 Jan 2021 10:51:38 GMT

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.

Re: Rank the duplicates values

AlB — Wed, 20 Jan 2021 13:49:22 GMT

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

Re: Rank the duplicates values

Paulshap — Wed, 20 Jan 2021 11:01:06 GMT

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

Re: Rank the duplicates values

Anonymous — Wed, 20 Jan 2021 11:29:43 GMT

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

Re: Rank the duplicates values

Paulshap — Wed, 20 Jan 2021 13:01:15 GMT

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