Forum Discussion

shuhn1229's avatar
shuhn1229
Icon for Resolver I rankResolver I
3 years ago
Solved

Max Date Per Category

Hi all,

 

I have a table such as the below:

 

ColorNameDate
PurpleJoe1/1/2023
GreenLarry1/1/2023
RedMoe1/1/2023
PurpleCurly5/1/2023
PurpleJoe5/1/2023
RedShemp1/1/2023

 

 

I am trying to remove duplicates, of Color category, removing earlier dates. Essentially, remove all records that are not equal to the max date per each of the color categories. The output would look like the below. Note that the first record of "purple" is removed as max is 5/1,  and all instances of "red" are kept as they equal the max date.

 

ColorNameDate
GreenLarry1/1/2023
RedMoe1/1/2023
PurpleCurly5/1/2023
PurpleJoe5/1/2023
RedShemp1/1/2023

 

 

Thanks,

 

Need Help
  • Arul's avatar
    Arul
    3 years ago

    shuhn1229 ,

    Use this code in calculated table,

    Max Date = 
VAR _tempTable = SUMMARIZE(
    'Table',
    'Table'[Color],
    'Table'[Name],
    "@Date",MAX('Table'[Date]))
RETURN _tempTable

    Thanks,

    Arul

6 Replies

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    3 years ago

    shuhn1229 ,

    Can you try this measure?

    Max Date = 
VAR _tempTable = 
SUMMARIZE(
    'Table',
    'Table'[Color],
    "@Date",MAX('Table'[Date]))
VAR _result = 
CALCULATE(
    MAXX(_tempTable,[@Date]),
    'Table'[Date] = MAXX(_tempTable,[@Date]))
RETURN _result

    Thanks,

    Arul

  • shuhn1229's avatar
    shuhn1229
    Icon for Resolver I rankResolver I
    3 years ago

    dear Arul ,

     

    Sorry for not clarifying, i'd need this data to be a new table so that I can join it to the model.

     

    When creating a new table using that expression I get the following error: The expression specified in the query is not a valid table expression.

     

    Max Date =
    VAR _tempTable =
    SUMMARIZE(
    'Table',
    'Table'[Color],
    "@Date",MAX('Table'[Date]))
    VAR _result =
    CALCULATE(
    MAXX(_tempTable,[@Date]),
    'Table'[Date] = MAXX(_tempTable,[@Date]))
    RETURN _result

     

    I am replacing the bolded above with my data. Hopefully I am understanding your formula above?

     

    Steve

      • shuhn1229's avatar
        shuhn1229
        Icon for Resolver I rankResolver I
        3 years ago

        Hi Arul ,

         

        Thank you, I got this to work as a measure. How could I adapt this for a new table so that I could join the output to a model?

         

        Best,