- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Returning a different value based on duplicates
Hello, all...I am thankful you exist. 🙂
Here is my issue:
I am trying to do a pie chart that compares number of arrests where a mental health flag is present vs. when it is not.
I have duplicate rows if the arrest was for a person who had more than one type of flag.
Currently, I am using Count(Distinct) in the visualization filter so that an arrest is only considered once, however, it is counting one "Yes" and one "No" for each arrest number.
What I am looking for is a way in DAX to have the first instance of a duplicate that meets a condition say 'Yes' and any following values be blank if they are the same arrest number. That way I can filter out the blank values.
I currently have a simple IF statement: IF(table[column] = "MH", "Yes", "No") which returns:
Arrest # | Flag | MH Flag |
1 | MH | Yes |
1 | HXS | No |
1 | FEL | No |
2 | FEL | No |
3 | HXS | No |
I would like a DAX expression that returns the following based on the duplicate arrest numbers (Thank you so much in advance!):
Arrest # | Flag | MH Flag |
1 | MH | Yes |
1 | HXS | (Blank) |
1 | FEL | (Blank) |
2 | FEL | No |
3 | HXS | No |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please try
MH Flag =
IF (
"MH"
IN CALCULATETABLE (
VALUES ( 'Table'[Flag] ),
ALLEXCEPT ( 'Table', 'Table'[Asset #] )
),
IF ( 'Table'[Flag] = "MH", "Yes" ),
"No"
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
tamerj1,
You are a genius beyond measure. It worked. Thank you so much!!
Crystal
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please try
MH Flag =
IF (
"MH"
IN CALCULATETABLE (
VALUES ( 'Table'[Flag] ),
ALLEXCEPT ( 'Table', 'Table'[Asset #] )
),
IF ( 'Table'[Flag] = "MH", "Yes" ),
"No"
)

Helpful resources
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
05-05-2025 06:36 PM | |||
04-17-2025 10:28 AM | |||
06-15-2025 02:34 AM | |||
04-23-2025 07:48 PM | |||
03-06-2025 02:43 AM |
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |