Get distinct values in calculated table

[Sohan]

I have the following calculated table:

Client ID	Employee name	Date appointment
1	Employee X	5-1-2021
1	Employee Y	7-1-2021

I only want the first row to show. I only want the client ID to appear once, with the earliest date. How can I do this?

[selimovd · Accepted solution]

Hey @Sohan ,

if you want to get only the earliest date, the following measure should do the job:

First Date by client =
CALCULATE(
    MIN( myTable[Date appointment] ),
    ALLEXCEPT(
        myTable,
        myTable[Client ID]
    )
)

If you only want to show the first row, you also have to replace the employee column with the following measure:

First Employee = 
VAR vFirstDate = [First Date by client]
RETURN
CALCULATE(
    MIN( myTable[Employee name] ),
    ALLEXCEPT(
        myTable,
        myTable[Client ID]
    ),
    myTable[Date appointment] = vFirstDate
)

Then you should put the two measures in a table and you get the result you want:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[selimovd · Accepted solution]

Hey @Sohan ,

if you want to get only the earliest date, the following measure should do the job:

First Date by client =
CALCULATE(
    MIN( myTable[Date appointment] ),
    ALLEXCEPT(
        myTable,
        myTable[Client ID]
    )
)

If you only want to show the first row, you also have to replace the employee column with the following measure:

First Employee = 
VAR vFirstDate = [First Date by client]
RETURN
CALCULATE(
    MIN( myTable[Employee name] ),
    ALLEXCEPT(
        myTable,
        myTable[Client ID]
    ),
    myTable[Date appointment] = vFirstDate
)

Then you should put the two measures in a table and you get the result you want:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Sohan]

@selimovd I did something wrong. Your code works like a charm. Thank you so much for your help!

[Sohan]

Hi @selimovd, thanks for your response! The measure does not seem to give the desired result. This is the table I'm getting with it:

I want the table above (in the post), with all columns.

[selimovd]

Hey @Sohan ,

you also have to add the Client ID column and then the First Employee measure to your table.

If you just put the first date measure you will only see the first date overall.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Sohan]

@selimovd of course, thanks! I do get the unique client ID now, but I only get the very first date (5-1-2021) and first employee (employee x) for all rows. Even when this isn't correct in the data.

[selimovd]

Hey @Sohan ,

can you share a screenshot of the result?

I think that's easier than the description.

Best regards

Denis

[Sohan]

Hi @selimovd, please find the screenshot here:

[v-deddai1-msft · Accepted solution]

Hi @Sohan ,

You can create a visual level filter:

Measure = IF(MAX('Table'[Date appointment]) = CALCULATE(MIN('Table'[Date appointment]),ALLEXCEPT('Table','Table'[Client ID])),1,0)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[selimovd]

@Sohan did you put your Client ID column in the table?

If you use the Client ID from the table and the 2 measures it should work:

Best regards

Denis

[Sohan]

@selimovd the first column is the client ID.

[selimovd]

@Sohan then in your file there is something different from my example file 😏

Can you share the file? Like this, I could take a look directly into your case. Maybe you could remove or replace sensitive data.