Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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
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.
Hi @v-shex-msft && @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,
Hi,
What do you mean by Filter out? Should that row be removed or should null appear in that cell?
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,
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
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
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
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,
@Anonymous
is it good that all InvoiceNumber are completely the same inside each Document ID?
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.
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |