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
     
     

     

speedramps's avatar
speedramps
Icon for Super User rankSuper User
4 years ago

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.