Creating unique table without duplicate values

Hi Molin ,

To create a calculated table that lists unique [Reference] times for each transaction, selecting only the latest one based on [PostingTime], you need a DAX expression that can group by the transaction identifier and then isolate the single latest record within each group. Since you are using incremental refresh and cannot use Power Query to remove duplicates, a DAX-based solution is the correct approach. The challenge in your original formula is that it doesn't contain a step to evaluate the [PostingTime] and select only the record with the latest value for each [GuestCheckId].

A more effective approach is to use SUMMARIZE to group your data, combined with TOPN to find the latest record for each group. This allows you to first define the groups and then, for each one, perform a calculation to find the corresponding [Reference] from the single latest transaction. The following formula will produce the desired result by first filtering for the necessary [ObjectNumber], then grouping by [GuestCheckId], and finally creating a new column that retrieves the [Reference] from the row with the maximum [PostingTime] within that group.

PU_Times =
SUMMARIZE (
    FILTER (
        'TransactionDetails',
        'TransactionDetails'[ObjectNumber] = 999000009
    ),
    'TransactionDetails'[GuestCheckId],
    "Latest_Reference",
    CALCULATE (
        SELECTEDVALUE ( 'TransactionDetails'[Reference] ),
        TOPN (
            1,
            ALL ( 'TransactionDetails'[PostingTime], 'TransactionDetails'[Reference] ),
            'TransactionDetails'[PostingTime],
            DESC
        )
    )
)

In this revised DAX expression, the FILTER function first creates a temporary table containing only the rows where the ObjectNumber is 999000009. The SUMMARIZE function then iterates over this temporary table, creating a unique row for each GuestCheckId. The key part is the calculation for the new "Latest_Reference" column. Here, CALCULATE modifies the evaluation context. Inside it, TOPN sorts the transactions for the current GuestCheckId by PostingTime in descending order and returns a table containing only the top (latest) row. The SELECTEDVALUE function then safely extracts the [Reference] value from that single-row table, effectively giving you the reference from the latest transaction time for each GuestCheckId.