Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Ranking

Hi all,   I'm trying to rank each customer's purchases chronologically based on their purchase date starting with "1" for each customer's first purchase and then sequencing numercially with each ad...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Rank invoicenumber base measure: = 
RANKX (
    FILTER (
        ALL ( ep_Sales_Invoice_Line ),
        ep_Sales_Invoice_Line[Customer_Account_Number]
            = MAX ( ep_Sales_Invoice_Line[Customer_Account_Number] )
    ),
    CALCULATE ( MAX ( ep_Sales_Invoice_Line[INVOICENUMBER] ) ),
    ,
    ASC
)

     

    Rank invoicedate base measure: = 
RANKX (
    FILTER (
        ALL ( ep_Sales_Invoice_Line ),
        ep_Sales_Invoice_Line[Customer_Account_Number]
            = MAX ( ep_Sales_Invoice_Line[Customer_Account_Number] )
    ),
    CALCULATE ( MAX ( ep_Sales_Invoice_Line[Invoice_Date] ) ),
    ,
    ASC
)

     

    Rank measure: =
RANKX (
    FILTER (
        ALL ( ep_Sales_Invoice_Line ),
        ep_Sales_Invoice_Line[Customer_Account_Number]
            = MAX ( ep_Sales_Invoice_Line[Customer_Account_Number] )
    ),
    [Rank invoicedate base measure:] + [Rank invoicenumber base measure:] / 100,
    ,
    ASC
)
rodrigosan's avatar
rodrigosan
Responsive Resident
4 years ago

Hello, Anonymous how are you?
I don't know if my solution can help you.
What I did was create a key in the fSales table with the date in numeric format (yyyymmdd) and with the CustomerKey.
After I duplicated this table, I left only the Data / CustomerKey / Key columns.
I created a group and inserted an index inside the group.
Then I expanded and merged with fSales table based on Key.
Then I disabled the copy load.
I have my example file here at this link: https://easyupload.io/mjew8f

 

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/