Change the amount of a value in a column if certain conditions are met

[datatbl123]

Hi,

I am looking for a DAX formula that would create a count column and would return "1" or "0.5" but only return "0.5" if the person's name in my Scheduled Person column is the same 2 days in a row (back to back days) from my Appt date column. Also the Procedure needs to be equal to A and the Center needs to be equal B.

Thank you,

[FreemanZ]

hi @datatbl123 

how about more than 2 same days in a row? Or it doesnt exist?

[datatbl123]

@FreemanZ, likely not to happen

[FreemanZ]

hi @datatbl123 

try to add a column with this:

Count2 = 
VAR _patient = [Person]
VAR _date = [Date]
VAR _value = 
AVERAGEX(
    FILTER(
        TableName,
        TableName[Person]=_patient
            && TableName[Center] = "B"
            && TableName[Procedure] = "A"
    ),
    TableName[Date]
)
RETURN
IF(_value=_date+0.5||_value=_date-0.5, 0.5,1 )

i tried and it worked like this:

[FreemanZ · Accepted solution]

A solution below would have better performance :

Count3 = 
VAR _patient = [Person]
VAR _date = [Date]
VAR _value =
COUNTROWS(
    FILTER(
        TableName,
        TableName[Person]=_patient
            && TableName[Center] = "B"
            && TableName[Procedure] = "A"
            && ([Date]=_date+1 || [Date]=_date-1)
    )
)
RETURN
IF(_value=1, 0.5, 1 )