Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I want to rank within the InvoiceNumber group by CreatedDate, the latest date should be rank 1. There are two InvoiceNumber in the below data 408 and 449.
I have created a measure to do the ranking:
InvoiceRankInTimePeriod =
VAR CreatedDate = [CreatedDateMin]
VAR Invoices =
CALCULATETABLE ( 'Data InvoiceFact', ALLSELECTED ( 'Data InvoiceFact' ), Values('Data InvoiceFact'[InvoiceNumber]) )
VAR InvoiceRank =
RANKX ( Invoices, VALUE(FORMAT([CreatedDateMin], "YYYYMMDDHHmmss") + [InvoiceKeyMin]))
VAR Result =
IF ( NOT ISBLANK ( CreatedDate ), InvoiceRank )
RETURN
Result
The ranking works:
I am wondering why it works, in particular what is VALUES doing within the CALUCLATETABLE function.
Hi,
The VALUES function creates a virtual table with all unique values of Invoice Number.
It's very close to DISTINCT function, even though soem diffrences might appear in some calculations.
To have more details on both functions, please refer to :
and
Hope it makes more sense for you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |