Row count of one column and returning the max value

[japolo]

Hi,

The subject title was a bit hard to make sense of but i'm trying to explain the issue below.

I have a table with multiple columns where the ID is the same, but the timestamp, shape and sequence number is different. 

What I want to return is the count of 1 based on shape. The sequence number is higher based on the last shape selected, and the max timestamp_when_select is the latest selection. So in the image below, "2021-11-30 03:53:56 with the nbr of 25 is the one I want to return.
How do I make it correct so the shape "planned2D_rec" gets the count and no other shape)

Kindly,

J

[v-yalanwu-msft · Accepted solution]

Hi, @japolo ;

 You could try it.

Measure = CALCULATE(COUNT('Table'[shape]),FILTER(ALL('Table'),[shape]=
CALCULATE(MAX('Table'[shape]),FILTER('Table',[timestamp]= CALCULATE(MAX('Table'[timestamp]),ALLEXCEPT('Table','Table'[planning_id]))))))

The final show：

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @japolo 
Please try

New Column =
VAR CurrentIDTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Planning_id] ) )
VAR SummaryTable =
    SUMMARIZE ( CurrentIDTable, TableName[shape] )
VAR RequiredTable =
    GENERATE (
        SummaryTable,
        VAR Count =
            CALCULATE ( COUNTROWS ( TableName ) )
        VAR LastTimestamp =
            CALCULATE ( MAX ( TableName[timestamp_when_select] ) )
        VAR SequenceNbr =
            CALCULATE (
                MAX ( TableName[sequence_nbr] ),
                TableName[timestamp_when_select] = LastTimestamp
            )
        RETURN
            ROW ( "@Count", Count, "@SequenceNbr", SequenceNbr )
    )
VAR TopRecord =
    TOPN ( 1, RequiredTable, [@Count] )
RETURN
    MAXX ( TopRecord, [@SequenceNbr] )