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 specif...
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.

Fowmy's avatar
Fowmy
Icon for Super User rankSuper 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"
)