Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Im trying to write a RANKX expression to capture the ranking of transactions at a customer invoice level. But taking into account the date / calendar filters I have in place on the report (date slicer).
All transactions have a unique index called transaction id and this is an incremental field, the the larger the number the more recent the transaction naturally.
At the moment the calculated column I am using is:
Solved! Go to Solution.
You got me on the right path though - I think this is working now with the ALLSELECTED 'calendar[Date] instead:
Hi,
If you are writing that formula as a calculated column formula (as you hav mentioned), then a selection/change in a slicer will not have any effect on a calculated column formula unless you hit refresh. Without refreshing, a selection/change in a slicer will only effect measures.
So I need to use a measure? how can I write this to work as a measure then? I believe the context doesnt work for EARLIER/EALIEST in a measure? Any help would be appreciated.
Yes, you need to write a measure. Share some data (in a format that can be pasted in an MS Excel file), describe the question and show the expected result.
Transaction ID | Transaction Date | Transaction Type | Invoice Code | Amount | Balance |
1 | 10/07/2021 | Add | INV0001 | 10 | 10 |
2 | 12/07/2021 | Add | INV0002 | 20 | 20 |
3 | 12/07/2021 | Payment | INV0002 | 10 | 10 |
4 | 14/07/2021 | Credit Note | INV0002 | 5 | 5 |
Example, INV0002 id expext to be rank number 1 if the date selected on the report is for transactions between 10/07 and 12/07 (date slicer). Does that help give context?
Hi,
You may download my PBI file from here.
Hope this helps.
Very close - how does it take the context of each INVOCIE Code? e.g. transaction id 1 would also be ranked 1 as its the only transaction for INV00001?
Hi,
Try this measure
Rank1 = if(ISBLANK([Amt]),BLANK(),RANKX(GENERATE(all(Data[Transaction ID]),all(Data[Transaction Type])),[value of transaction id]))
Actually its not quite right -
moving the date to the 14th you can see INV0002 transaction ID 3 should have a rank 2 (under the measure Rank1. And transaction id 2 should be now ranked 3rd. Any ideas?
Hi,
Try this measure
Rank1 = if(ISBLANK([Amt]),BLANK(),RANKX(GENERATE(all('Calendar'[Date]),GENERATE(all(Data[Transaction ID]),all(Data[Transaction Type]))),[value of transaction id]))
Hope this helps.
You got me on the right path though - I think this is working now with the ALLSELECTED 'calendar[Date] instead:
Still not quite right as if you slide the date back to the 13th or the 12th ransaction id 3 should be ranked number 1 at this date:
Perfect - just trying to apply it to my orginal file now. Can I ask why you used this bit? trying to get my head around why this works. How does it know to rank them at an invoice code level?
all(Data[Transaction Type])
It is ranking all unique combinations of transaction id and transaction type within that invoice code.
If my previous reply helped, please mark it as Answer.
Sorry I spoke to soon - did you see my other reply? I only want to rank at the invoice code level, does my last post make sense?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |