Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello! I have data similar to the table below:
Claim ID | Survey Type | Score |
101 | Open | 9 |
101 | Closing | 8 |
101 | Immediate | 9 |
205 | Open | 10 |
205 | Open | 0 |
326 | Immediate | 2 |
458 | Closing | 6 |
961 | Immediate | 7 |
111 | Closing | 9 |
596 | Open | 3 |
I want to count the scores of ALL the survey responses EXCEPT those duplicate survey responses that are NOT Closing, but ONLY IF a Closing response has been submitted FOR THAT Claim ID. In other words, IF there is a Closing response AND that response is a duplicate, then count that Closing response and exclude all other responses FOR THAT Claim ID, ELSE Do Not Exclude.
Any help is greatly appreciated! Happy to answer clarifying questions!
Solved! Go to Solution.
Hi bemunni
Click here to download example solution
I am not sure if I have understood, next time please add example of the desired output to help your explanation.
In my attached example I added DAX measurs with comments.
The Has closing and Has duplicate measure shoukld help you get what you need.
I helped you, now please can you help me with kudos.
I am a unpaid Power BI volunteer.
Please click the thumbs up and Accept as Solution for taking time to help you.
Thank you 😁
I am an unpaid power Bi volunteer. Please click solved to accept the solution so we get kudos. If your problem has expanded or changed then set this one to solved and raise a new ticket, because this solution does seem to answer the original question. Also try include example of the imput and desired output with a decsription so we kneo what you need. Many thanks.
Hi bemunni
Click here to download example solution
I am not sure if I have understood, next time please add example of the desired output to help your explanation.
In my attached example I added DAX measurs with comments.
The Has closing and Has duplicate measure shoukld help you get what you need.
The result is to pass the count of the non-excluded scores (by Type, i.e. 8-10 = Promoter, 6-7 = Passive, Else Detractor) into another formula that calculates a net promoter score. So, it's actually Count of Score Type EXCLUDING those Score Types I want exluded as previously mentioned. The table should actually look more like this:
Claim ID | Survey Type | Score | Score Type |
101 | Open | 9 | Promoter |
101 | Closing | 8 | Promoter |
101 | Immediate | 9 | Promoter |
205 | Open | 10 | Promoter |
205 | Open | 0 | Detractor |
326 | Immediate | 2 | Detractor |
458 | Closing | 6 | Passive |
961 | Immediate | 7 | Passive |
111 | Closing | 9 | Promoter |
596 | Open | 3 | Detractor |
Although, I may have not understood clearly your need becuase both your explanations are bit confusing evn with much effort you put to explain. Based on what I assumed, pls confirm if you need to see something like this as below and then filter the count column which are >1 and Promoter, i.e. claim ID 101 your case:
What's the desired result from the provided data?
It will be helpful if you could explain the result according to the algorithm you describe above.
And, when you say count, do you mean count, or sum ?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
71 | |
55 | |
37 | |
32 |
User | Count |
---|---|
71 | |
65 | |
60 | |
50 | |
45 |