Forum Discussion
Filtering out Data Conditionally
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!
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 0VAR myclaimID = SELECTEDVALUE(Facts[Claim ID])VAR myset = FILTER(ALL(Facts),Facts[Claim ID] = myclaimId && Facts[Survey Type] = "Closing")RETURNINT(NOT(ISEMPTY(myset)))Has duplicates =-- retuns 1 if claim has any dulicates otherwise returns 0VAR myclaimID = SELECTEDVALUE(Facts[Claim ID])VAR myset = FILTER(ALL(Facts),Facts[Claim ID] = myclaimId)RETURNIF(COUNTROWS(myset) > 1, 1, 0)Responses =VAR myclaimID = SELECTEDVALUE(Facts[Claim ID])VAR myset = FILTER(ALL(Facts),Facts[Claim ID] = myclaimId)RETURNCOUNTROWS(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
6 Replies
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 0VAR myclaimID = SELECTEDVALUE(Facts[Claim ID])VAR myset = FILTER(ALL(Facts),Facts[Claim ID] = myclaimId && Facts[Survey Type] = "Closing")RETURNINT(NOT(ISEMPTY(myset)))Has duplicates =-- retuns 1 if claim has any dulicates otherwise returns 0VAR myclaimID = SELECTEDVALUE(Facts[Claim ID])VAR myset = FILTER(ALL(Facts),Facts[Claim ID] = myclaimId)RETURNIF(COUNTROWS(myset) > 1, 1, 0)Responses =VAR myclaimID = SELECTEDVALUE(Facts[Claim ID])VAR myset = FILTER(ALL(Facts),Facts[Claim ID] = myclaimId)RETURNCOUNTROWS(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, speedrampsWhat'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 ?
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:
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.
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 😁
