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

[datatbl123]

Hi,

I would like for the value in my Count column to change from a 1 to a 0.5 but only 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.

[v-xiaosun-msft]

Hi @datatbl123 ,

According to your description, I made the sample and here is my solution.

Create two columns to return the rows which meet the requirement.

Column = 'Table'[Appt Date]+1

Column 2 =
VAR a =
    FILTER (
        'Table',
        [Column] = EARLIER ( 'Table'[Appt Date] )
            && 'Table'[Scheduled Person] = EARLIER ( 'Table'[Scheduled Person] )
    )
VAR b =
    FILTER (
        'Table',
        EARLIER ( 'Table'[Scheduled Person] ) = 'Table'[Scheduled Person]
            && MONTH ( 'Table'[Appt Date] ) = MONTH ( EARLIER ( 'Table'[Appt Date] ) )
    )
VAR c =
    MAXX ( b, [Appt Date] ) - COUNTROWS ( b ) + 1 = [Appt Date]
RETURN
    IF (
        COUNTAX ( A, [Scheduled Person] ) > 0,
        TRUE (),
        IF ( C = TRUE () && COUNTROWS ( B ) > 1, TRUE (), FALSE () )
    )

Please mind that when you use dax, it will add a new column instead of changing your count column from a 1 to a 0.5. So create three columns to get your expected output.

Center = IF('Table'[Column 2]=TRUE(),"B")

Process = IF('Table'[Column 2]=TRUE(),"A")

Count = IF('Table'[Column 2]=TRUE(),0.5,1)

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[datatbl123]

@v-xiaosun-msft , thank you for your reply but unfortunately your solution does not work as you can see below. The "1" in the red square should be "0.5" since it has the same scheduled person and the dates are back to back. Do you have an alternative solution?

[datatbl123]

@v-xiaosun-msft 

Hi, the last solutiom received also does not work when adding variable d.

[v-xiaosun-msft]

Hi @datatbl123 ,

Yes, I relized that adding a variable d was not the solution, so I undid it as that time.

Sorry for being late. I modified the sample, and below is my solution.

Firstly, use Power Query to create an Index column.

Then, create a calculated column to return TRUE or FALSE.

rows =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Appt Date] ),
        FILTER (
            'Table',
            'Table'[Scheduled Person] = EARLIER ( 'Table'[Scheduled Person] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) + 1
        )
    )
VAR _b =
    CALCULATE (
        MAX ( 'Table'[Appt Date] ),
        FILTER (
            'Table',
            'Table'[Scheduled Person] = EARLIER ( 'Table'[Scheduled Person] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) - 1
        )
    )
RETURN
    IF (
        ( 'Table'[Appt Date] = _a + 1
            || 'Table'[Appt Date] = _a - 1
            || 'Table'[Appt Date] = _b + 1
            || 'Table'[Appt Date] = _b - 1 ),
        TRUE (),
        FALSE ()
    )

Then create columns to return "center", "process" and "count" according to the previous formula.

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[datatbl123 · Accepted solution]

Thank you for the revised solution @v-xiaosun-msft  but I went with the following instead:

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 )

[v-xiaosun-msft]

Hi @datatbl123 ,

Glad that you get the expected solution. If you don't have other problems, could you please accept the answer you need as the solution? Then we are able to close the thread. Thank you!

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.