Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • speedramps's avatar
    speedramps
    4 years ago

    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
     
     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 IDSurvey TypeScoreScore Type
101Open9Promoter
101Closing8Promoter
101Immediate9Promoter
205Open10Promoter
205Open0Detractor
326Immediate2Detractor
458Closing6Passive
961Immediate7Passive
111Closing9Promoter
596Open3Detractor

 

mahenkj2's avatar
mahenkj2
Solution Sage
4 years ago

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: