Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 #FlagMH Flag
1MHYes
1HXSNo
1FELNo
2FELNo
3HXSNo

 

I would like a DAX expression that returns the following based on the duplicate arrest numbers (Thank you so much in advance!):

 

Arrest #FlagMH Flag
1MHYes
1HXS(Blank)
1FEL(Blank)
2FELNo
3HXSNo
  • Hi Anonymous 

    Please try

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    tamerj1,

     

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

     

    Crystal

  • tamerj1's avatar
    tamerj1
    Community Champion

    Hi Anonymous 

    Please try

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