Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Reference RankX Value from a Summarized Table

Hello Everyone.

 

I'm trying to find the 2nd invoice Date created by customer in my database and I'm having a hard time doing this with RankX.

 

I need to use RankX because it has to be dynamic.

 

Below is the RankX formula I'm currently using:

Customer Invoice Rank =

VAR CURRENTCUSTOMER = MAX('Cart'[Customer_ID])
RETURN
RANKX( FILTER( ALLSELECTED( 'Cart'),'Cart'[Customer_ID]=CURRENTCUSTOMER),[Invoice Date (Min)],,ASC,Dense)
 
So far gotten it to SumX the Rank Values for each customer, but I really need to reference the Date for a certain Rank value.
 Rank Test SumX =
VAR NEWTABLE = SUMMARIZE( 'Cart', 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date])
RETURN
GROUPBY(
            ADDCOLUMNS(
                              GROUPBY(NEWTABLE, 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date]) ,
                              "The Rank",[Customer Invoice Rank]
                                     )
            ,"Value" ,SUMX(CURRENTGROUP(),[The Rank])
)
 
In the end I really need it do below but I cant use Calculate in this situation: 
Min Invoice Rank 2 =
VAR NEWTABLE = SUMMARIZE('Cart', 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date])
RETURN
GROUPBY(
          ADDCOLUMNS(
                           GROUPBY( NEWTABLE, 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date]) ,
                           "Rank",[Customer Invoice Rank]
                                  )
           ,"Value",CALCULATE( MIN( 'Cart'[Invoice_Date]), FILTER( CURRENTGROUP(), [Rank]=2))
)
 
Does anyone know how to reference the Rank in this situation?
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I have updated the formula but its still not filtering for Rank 2 and only showing the Max Date.

     

    Invoice Date (Min) R2 =

    VAR NEWTABLE = SUMMARIZE( 'Cart', 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date], "Rank" ,[Customer Invoice Rank])
     
    RETURN
     
    CALCULATE(
              GROUPBY(
                   ADDCOLUMNS( GROUPBY (NEWTABLE, 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date], [Rank]) ,"Invoice Min",[Invoice Date (Min)])
                  ,"Value",( MAXX( CURRENTGROUP(), [Invoice Min] ) )
                  )
    ,FILTER(NEWTABLE,[Rank]=2))
  • Anonymous's avatar
    Anonymous
    6 years ago

    Solved: I put the Calculate inside the addcolumns

     

    Invoice Date (Min) R2 =

    VAR NEWTABLE = SUMMARIZE('(SQL) Cart','(SQL) Cart'[Customer_ID],'(SQL) Cart'[Invoice_ID],'(SQL) Cart'[Invoice_Date],"Rank",[Customer Invoice Rank])
    RETURN
    GROUPBY(
    ADDCOLUMNS(
    GROUPBY(NEWTABLE,'(SQL) Cart'[Customer_ID],'(SQL) Cart'[Invoice_ID],'(SQL) Cart'[Invoice_Date],[Rank])
    ,"Invoice Min",CALCULATE([Invoice Date (Min)],FILTER(NEWTABLE,[Rank]=2))
    )
    ,"Value",(MAXX(CURRENTGROUP(),[Invoice Min]))
    )

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello Everyone.

     

    I'm trying to find the 2nd invoice Date created by customer in my database and I'm having a hard time doing this with RankX.

     

    I need to use RankX because it has to be dynamic.

     

    Below is the RankX formula I'm currently using:

    Customer Invoice Rank =

    VAR CURRENTCUSTOMER = MAX('Cart'[Customer_ID])
    RETURN
    RANKX( FILTER( ALLSELECTED( 'Cart'),'Cart'[Customer_ID]=CURRENTCUSTOMER),[Invoice Date (Min)],,ASC,Dense)
     
    So far gotten it to SumX the Rank Values for each customer, but I really need to reference the Date for a certain Rank value.
     Rank Test =
    VAR NEWTABLE = SUMMARIZE( 'Cart', 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date])
    RETURN
    GROUPBY(
                ADDCOLUMNS(
                                  GROUPBY(NEWTABLE, 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date]) ,
                                  "The Rank",[Customer Invoice Rank]
                                         )
                ,"Value" ,SUMX(CURRENTGROUP(),[The Rank])
    )
     
    In the end I really need it do below but I cant use Calculate in this situation: 
    Min Invoice Rank 2 =
    VAR NEWTABLE = SUMMARIZE('Cart', 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date])
    RETURN
    GROUPBY(
              ADDCOLUMNS(
                               GROUPBY( NEWTABLE, 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date]) ,
                               "Rank",[Customer Invoice Rank]
                                      )
               ,"Value",CALCULATE( MIN( 'Cart'[Invoice_Date]), FILTER( CURRENTGROUP(), [Rank]=2))
    )
     
    Does anyone know how to reference the Rank in this situation?
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I have updated the formula but its still not filtering for Rank 2 and only showing the Max Date.

     

    Invoice Date (Min) R2 =

    VAR NEWTABLE = SUMMARIZE( 'Cart', 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date], "Rank" ,[Customer Invoice Rank])
     
    RETURN
     
    CALCULATE(
              GROUPBY(
                   ADDCOLUMNS( GROUPBY (NEWTABLE, 'Cart'[Customer_ID], 'Cart'[Invoice_ID], 'Cart'[Invoice_Date], [Rank]) ,"Invoice Min",[Invoice Date (Min)])
                  ,"Value",( MAXX( CURRENTGROUP(), [Invoice Min] ) )
                  )
    ,FILTER(NEWTABLE,[Rank]=2))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Solved: I put the Calculate inside the addcolumns

       

      Invoice Date (Min) R2 =

      VAR NEWTABLE = SUMMARIZE('(SQL) Cart','(SQL) Cart'[Customer_ID],'(SQL) Cart'[Invoice_ID],'(SQL) Cart'[Invoice_Date],"Rank",[Customer Invoice Rank])
      RETURN
      GROUPBY(
      ADDCOLUMNS(
      GROUPBY(NEWTABLE,'(SQL) Cart'[Customer_ID],'(SQL) Cart'[Invoice_ID],'(SQL) Cart'[Invoice_Date],[Rank])
      ,"Invoice Min",CALCULATE([Invoice Date (Min)],FILTER(NEWTABLE,[Rank]=2))
      )
      ,"Value",(MAXX(CURRENTGROUP(),[Invoice Min]))
      )