Forum Discussion

Pmike's avatar
Pmike
Advocate I
8 years ago
Solved

Creating New Table by Summarizing rows based on Latest Date

I have the following question on creating a new table.   My table consists of the following example:   CURRENT TABLE Customer     Date             Image A                  10/1/2017    Image 1 ...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Pmike

     

    If you want a calculated table. Try this.
    Go to modelling tab...Press "New Table"

     

    New Table =
ADDCOLUMNS (
    SUMMARIZE (
        CurrentTable,
        CurrentTable[Customer],
        "Max Date", MAX ( CurrentTable[Date] )
    ),
    "Image", CALCULATE (
        SELECTEDVALUE ( CurrentTable[Image] ),
        FILTER (
            CurrentTable,
            CurrentTable[Date] = [Max Date]
                && CurrentTable[Customer] = EARLIER ( CurrentTable[Customer] )
        )
    )
)
parry2k's avatar
parry2k
Super User
8 years ago

Add a column using following expression to flag the most recent record and then filter on that

 

Recent = 
var r = RANKX(FILTER(Image, Image[Customer] = EARLIER(Image[Customer])), Image[Date], , DESC, DENSE)
return if(r = 1, "Yes", "")

 

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Pmike

     

    If you want a calculated table. Try this.
    Go to modelling tab...Press "New Table"

     

    New Table =
ADDCOLUMNS (
    SUMMARIZE (
        CurrentTable,
        CurrentTable[Customer],
        "Max Date", MAX ( CurrentTable[Date] )
    ),
    "Image", CALCULATE (
        SELECTEDVALUE ( CurrentTable[Image] ),
        FILTER (
            CurrentTable,
            CurrentTable[Date] = [Max Date]
                && CurrentTable[Customer] = EARLIER ( CurrentTable[Customer] )
        )
    )
)
    • Pmike's avatar
      Pmike
      Advocate I
      8 years ago

      Thanks Zubair, this was exactly what I was looking to accomplish.