Forum Discussion
Jadegirlify
Helper I
Helper IHow can I Create A Table Showing Distinct Occurrence Of A Specific Column Values in PowerBI?
| 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 |
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 1or create a measureMeasure = 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 1pls see the attachment below
6 Replies
ryan_mayuSuper User
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 1or create a measureMeasure = 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 1pls see the attachment below- Ashish_Mathur
Hi,
This measure works
Measure = if(DISTINCTCOUNT(Data[Clearance_Type])=1,MIN(Data[Clearance_Type]),BLANK())
JadegirlifyThanks, for some reason. I am getting this error.
- Ashish_Mathur
You are welcome. I cannot comment unless i see the file.
- Jadegirlify
This worked! Thanks so much 🙂
- ryan_mayu
you are welcome
