cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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
1 ACCEPTED SOLUTION
Super User

MH Flag =
IF (
"MH"
IN CALCULATETABLE (
VALUES ( 'Table'[Flag] ),
ALLEXCEPT ( 'Table', 'Table'[Asset #] )
),
IF ( 'Table'[Flag] = "MH", "Yes" ),
"No"
)

2 REPLIES 2
Frequent Visitor

tamerj1,

You are a genius beyond measure. It worked. Thank you so much!!

Crystal

Super User

MH Flag =
IF (
"MH"
IN CALCULATETABLE (
VALUES ( 'Table'[Flag] ),
ALLEXCEPT ( 'Table', 'Table'[Asset #] )
),
IF ( 'Table'[Flag] = "MH", "Yes" ),
"No"
)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors