Forum Discussion

Daretoexplore's avatar
Daretoexplore
Icon for Advocate I rankAdvocate I
7 months ago
Solved

Random question

Hi all

 

I have a weird situation, and Im not sure how to tackle it.

 

My data is as follows:

 

Name.                Category.        Date

Anne Other.        Banana.          1st Feb

Anne Other.        Banana.          2nd Feb

Anne Other.        Apple.            30th Jan

John Smith.        Banana.           30th Jan

John Smith.        Banana.          2nd Feb

 

Im wondering if there's is a way to create a column that marks the latest row for each individual name, for each individual category, so in this example rows 2,3 and 5 would receive the marker where the others wouldn't.

 

Is there a way because Im tempted to say its impossible!

 

Many thanks

 

 

Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    7 months ago

    You could create a calculated column as below:

    Is Latest Row =
VAR CurrName = 'Table'[Name]
VAR CurrCat  = 'Table'[Category]
VAR CurrDate = 'Table'[Date]
VAR MaxDateForPair =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Name] = CurrName && 'Table'[Category] = CurrCat
        )
    )
RETURN
IF ( CurrDate = MaxDateForPair, 1, 0 )

3 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    7 months ago

    You could create a calculated column as below:

    Is Latest Row =
VAR CurrName = 'Table'[Name]
VAR CurrCat  = 'Table'[Category]
VAR CurrDate = 'Table'[Date]
VAR MaxDateForPair =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Name] = CurrName && 'Table'[Category] = CurrCat
        )
    )
RETURN
IF ( CurrDate = MaxDateForPair, 1, 0 )