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. ...
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]))
    )
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]))
    )