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
     
     

     

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
4 years ago

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 ?