Forum Discussion

jcarb0321's avatar
jcarb0321
New Member
5 years ago
Solved

Returning a value when considering multiple criteria in an array

Hello,

 

Basically I have a table of Dates, Patients, Providers, and Procedure Codes. I need to return another value if a Patient Visit on a particular Date, with a particular Provider has 2 specific Procedure Codes/Categories.

 

Svc_DateClinic IDPatient IDProvider IDProcedure Code IDProcedure Category ID

4/1/2021Denver1234James26Non-Covid
4/1/2021Denver1234James27Non-Covid
4/1/2021Denver1234James28Non-Covid
4/2/2021Colorado Springs2234Andrews02Office Visit
4/2/2021Colorado Springs2234Andrews02Office Visit
4/2/2021Colorado Springs2234Andrews29Covid Test
4/3/2021Aurora5545Grant09Covid Test
4/3/2021Aurora5545Grant30Office Visit
4/3/2021Aurora5545Grant31Office Visit
4/4/2021Parker2234Andrews02Office Visit
4/4/2021Parker2234Andrews07Office Visit
4/4/2021Parker2234Andrews32Office Visit
4/4/2021Denver5545James49Covid Vaccine
4/4/2021Denver5545James50Office Visit
4/5/2021Denver1234Andrews40Covid Vaccine

 

If a visit contains an Office Visit & a Covid Test, then I need to return a text. Similarly with visits that are only Office Visits and only Covid Vaccines. 

 

Please let me know if you have any questions!

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi jcarb0321 ,

    You can create this calculate column:

    A = 
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Procedure Category ID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Procedure Category ID] <> "Non-Covid"
                && 'Table'[Svc_Date] = EARLIER ( 'Table'[Svc_Date] )
                && 'Table'[Patient ID] = EARLIER ( 'Table'[Patient ID] )
                && 'Table'[Provider ID] = EARLIER ( 'Table'[Provider ID] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        _count >= 2, "Office Visit and Covid Test",
        _count = 1, "Only " & [Procedure Category ID]
    )

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    jcarb0321 

    Please add the following column and check:

    Visit Type = 
var __filter = ALlEXCEPT(Table2,Table2[Svc_Date],Table2[Patient ID],Table2[Provider ID])
var __table = CALCULATETABLE(VALUES(Table2[Procedure Category ID]),__filter) 
var __all = CALCULATE( COUNTROWS(Table2), __filter) 
VAR __testoffice = COUNTROWS(FILTER(__table , Table2[Procedure Category ID] in  {"Covid Test", "Office Visit"} )) >= 2
VAR __office = CALCULATE( COUNTROWS(Table2), __filter, Table2[Procedure Category ID] =  "Office Visit")  = __all
VAR __vaccine = CALCULATE( COUNTROWS(Table2),__filter, Table2[Procedure Category ID] =  "Covid Vaccine")  = __all
return
SWITCH(
    TRUE(),
    __testoffice=TRUE(),"Office Visit and Test",
    __office=TRUE(),"Office Visit only",
    __vaccine=TRUE(), "'Vaccine Only"
)

     

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi jcarb0321 ,

    You can create this calculate column:

    A = 
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Procedure Category ID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Procedure Category ID] <> "Non-Covid"
                && 'Table'[Svc_Date] = EARLIER ( 'Table'[Svc_Date] )
                && 'Table'[Patient ID] = EARLIER ( 'Table'[Patient ID] )
                && 'Table'[Provider ID] = EARLIER ( 'Table'[Provider ID] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        _count >= 2, "Office Visit and Covid Test",
        _count = 1, "Only " & [Procedure Category ID]
    )

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.