How can I Create A Table Showing Distinct Occurrence Of A Specific Column Values in PowerBI?

[Jadegirlify]

Employee_ID	FULL_NAME	Clearance_Type
101	Amy Lu	Secret
102	Joe Boy	Polygraph
102	Joe Boy	Secret
103	Sam Jack	Confidential
103	Sam Jack	Confidential
103	Sam Jack	Confidential
104	Tom Smith	Secret
104	Tom Smith	Confidential
105	Ann Wilson	Secret
106	Mark Brown	Secret
106	Mark Brown	Secret
107	Jane Doe	Polygraph
107	Jane Doe	Secret
107	Jane Doe	Polygraph
108	Tim Bark	Confidential
108	Tim Bark	Secret
108	Tim Bark	Polygraph
109	May Alec	Confidential

I Need to create a table visual showing the result below.  I have tried multiple codes but none works. What query/measure should I be using?
Please note that employee_ID 103 & 106 were duplicated, but the clearance type is still only one type "Confidential".

Employee_ID	FULL_NAME	Clearance_Type
101	Amy Lu	Secret
103	Sam Jack	Confidential
105	Ann Wilson	Secret
106	Mark Brown	Secret
109	May Alec	Confidential

[Jadegirlify]

This worked! Thanks so much 🙂

[ryan_mayu]

you are welcome

[Ashish_Mathur]

Hi,

This measure works

Measure = if(DISTINCTCOUNT(Data[Clearance_Type])=1,MIN(Data[Clearance_Type]),BLANK())

[Jadegirlify]

Thanks, for some reason. I am getting this error. 

[Ashish_Mathur]

You are welcome.  I cannot comment unless i see the file.

[ryan_mayu · Accepted solution]

@Jadegirlify 

you can try to create a column

Column = if(maxx(FILTER('Table','Table'[Employee_ID]=EARLIER('Table'[Employee_ID])&&('Table'[FULL_NAME]<>EARLIER('Table'[FULL_NAME])||'Table'[Clearance_Type]<>EARLIER('Table'[Clearance_Type]))),'Table'[FULL_NAME])="",1,0)

then you add the column to visual filter and set to 1

or create a measure

Measure = if(maxx(FILTER(all('Table'),'Table'[Employee_ID]=max('Table'[Employee_ID])&&('Table'[Clearance_Type]<>max('Table'[Clearance_Type])||'Table'[FULL_NAME]<>max('Table'[FULL_NAME]))),'Table'[FULL_NAME])="",1,0)

and add the measure to the visual filter and set to 1

pls see the attachment below