Filtering out Data Conditionally

[beemunni]

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!

[speedramps]

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 😁

[speedramps]

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.

[speedramps · Accepted 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.

Has closing =

-- retuns 1 if claim has any closing surveys otherwise returns 0

VAR myclaimID = SELECTEDVALUE(Facts[Claim ID])

VAR myset = FILTER(ALL(Facts),Facts[Claim ID] = myclaimId && Facts[Survey Type] = "Closing")

RETURN

INT(NOT(ISEMPTY(myset)))

Has duplicates =

-- retuns 1 if claim has any dulicates otherwise returns 0

VAR myclaimID = SELECTEDVALUE(Facts[Claim ID])

VAR myset = FILTER(ALL(Facts),Facts[Claim ID] = myclaimId)

RETURN

IF(COUNTROWS(myset) > 1, 1, 0)

Responses =

VAR myclaimID = SELECTEDVALUE(Facts[Claim ID])

VAR myset = FILTER(ALL(Facts),Facts[Claim ID] = myclaimId)

RETURN

COUNTROWS(myset)

Answer =

--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.

SWITCH(TRUE(),

--If there is a Closing response AND that response is a duplicate, then count that Closing response

[Has closing] = 1 && [Has duplicates], 1,

-- Else Do Not Exclude.

[Responses]

)

I am an unpaid Power BI volunteer. Please click the thumbs up if you like me trying to help you. Also click solved if I fixed your problem. One problem per ticket please. If you need to expanr or change your problem then click solved on this one and raise a new ticket. Thank you and watm regards, speedramps

[beemunni]

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

[mahenkj2]

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:

[HotChilli]

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 ?