Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Please try
MH Flag =
IF (
"MH"
IN CALCULATETABLE (
VALUES ( 'Table'[Flag] ),
ALLEXCEPT ( 'Table', 'Table'[Asset #] )
),
IF ( 'Table'[Flag] = "MH", "Yes" ),
"No"
)
tamerj1,
You are a genius beyond measure. It worked. Thank you so much!!
Crystal
Please try
MH Flag =
IF (
"MH"
IN CALCULATETABLE (
VALUES ( 'Table'[Flag] ),
ALLEXCEPT ( 'Table', 'Table'[Asset #] )
),
IF ( 'Table'[Flag] = "MH", "Yes" ),
"No"
)
User | Count |
---|---|
17 | |
17 | |
14 | |
13 | |
13 |
User | Count |
---|---|
17 | |
14 | |
11 | |
10 | |
8 |