Forum Discussion
Get distinct values in calculated table
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?
Hey Anonymous ,
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicHi Anonymous ,
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
11 Replies
- selimovd
Most Valuable Professional
Hey Anonymous ,
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicselimovd I did something wrong. Your code works like a charm. Thank you so much for your help!
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 Anonymous ,
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic