How to find Rank on 2 Columns with duplicate values

[Anonymous]

Hi,

I need to find a Rank on combination of 2 columns DocumentID, InoviceNumber.

See the the sample data,

Expected Result as below,

Please help me in achieveing the desired output(Rank).

A quick help would be much appreciated

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0MlbSUYqINFSK1SHEdYJwI6OMsHGdoYqjjAlxYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Document ID" = _t, InvoiceNumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document ID", type text}, {"InvoiceNumber", type text}}),
    Partition = Table.Group(#"Changed Type", {"Document ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"InvoiceNumber", "Index"}, {"InvoiceNumber", "Index"})
in
    #"Expanded Partition"

Hope this helps.

[Anonymous]

Hi @Anonymous  && @Ashish_Mathur 

Thank you for the reply. Both the solutions are worked. But I have forgotten to include further condition,

As I have 80 columns including Document ID,InvoiceNumber and need to calculate Rank on Document ID, InvoiceNumber there is an filter condition while calculating the rank.

Basically I am converting content from Spotfire to Power BI, Let me put you the Spotfire Calculation for Rank that they were using

Spotfire calc:

Rank = case when [process] in ("open","In Progress") then Rank(Rowid(),[Document ID],[InvoiceNumber])end

In the process of converting the same into power bi the process that you have shared is working some extent. can you help me in achieveing the Rank including Process filter.

See the below sample data and desired output,

[Ashish_Mathur]

Hi,

What do you mean by Filter out?  Should that row be removed or should null appear in that cell?

[Anonymous]

Hi @Ashish_Mathur 

Filter out means we should exclude that record if process is closed and Rank should get calculated for remaining records.

Rank should get calculated on Document ID,InvoiceNumber and process not in Closed.

See the below example,

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can add a filter on process filed(before group steps), then custom index column will be change based on filtered records.
If you mean these records need to be changes based on visual level filter/slicer, it is impossible. Current power bi not able to use them to interact with query tables records. (visual level data are generated from data model tables, data model table records are generated from query tables, you can't use child-level records to effect its parent)

Regards,

Xiaoxin Sheng

[Anonymous]

HI @Anonymous ,

I'd like to suggest you enter to query editor to add an index column to achieve your requirement.

Steps:

1. Group table records by Document id, aggerate mode to 'all rows'.

2. Modify group steps to add index into grouped tables.

3. Click expand icon to expand table column, then modify the formula to add display index column.

Full query sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXI0VIrVwcV0AjKdMJjOQKYzTmYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Invoice = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Invoice", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Contents", each Table.AddIndexColumn(_,"Index",1,1), type table [ID=text, Invoice=text]}}),
    #"Expanded Contents" = Table.ExpandTableColumn(#"Grouped Rows", "Contents", {"Invoice","Index"}, {"Invoice","Index"})
in
    #"Expanded Contents"

Regards,

Xiaoxin Sheng

[az38]

Hi @Anonymous 

try calculated column

Column = RANKX(FILTER('Table1';'Table1'[Document ID]=EARLIER(Table1[Document ID]));'Table1'[InvoiceNumber];;ASC;Skip)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Hi @az38 ,

The below calculation gives me Rank 1 for all records. I would need to populate the Rank with combination of Document ID & Invoicenumber.

See the result below using the calculation,

Expected result should be,

[az38]

@Anonymous 

is it good that all InvoiceNumber are completely the same inside each Document ID?

[Anonymous]

Hi @az38 ,

We have data like that. If the both Documentid,Invoicenumber has duplicates it should give ranking as no of duplicates in asc order.