Creating unique table without duplicate values

[Molin]

Dear community, 

I am trying to create a calculated table using DAX to list the unique [Reference] times for each transaction. However, some transactions have more than one [reference] time and I would like the list only the latest based on [PostingTime].

So far I have managed to create the table, however filtering on one unique row is causing problems.

PU Times =
CALCULATETABLE (
    DISTINCT (
        SUMMARIZE (
            'TransactionDetails',
            'TransactionDetails'[Reference],
            'TransactionDetails'[GuestCheckId]
        )
    ),
    ''TransactionDetails''[ObjectNumber] = 999000009,
    DISTINCT ( 'TransactionDetails'[GuestCheckId] )
)

The large 'TransactionDetails' table looks like this, filtered on [ObjectNumber] and a single transaction

As I am using incremental refresh, it prohibit me from "removing doublicates" in Power Query. 

Thank you very much in advance.

[DataNinja777]

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.

[Molin]

Hi @DataNinja777 , 

Thank you so much for the quick reply and detailed explanation on the formula. I have tried to follow your steps and get unique values for all relevant [GuestCheckId], however the new calculated column "Latest_Reference" is blank for all rows. 

Can the datatype effect the statement? 'TransactionDetails'[PostingTime] is Date/Time format, while TransactionDetails'[Reference] is a String. 

Thank you so much for your time and sharing knowlegde. 

Best Regards

[speedramps]

I want to help you but your description is too vague. Please write it again.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). ‌‌
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.