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

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