Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
datatbl123
Helper I
Helper I

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

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.

 

example.jpg

 

 

 

1 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 )

 

 

View solution in original post

6 REPLIES 6
v-xiaosun-msft
Community Support
Community Support

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:

vxiaosunmsft_0-1670580155902.png

 

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.

@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?

 

1.jpg

@v-xiaosun-msft 

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

 

2.jpg

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:

vxiaosunmsft_0-1671592639335.png

 

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.

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 )

 

 

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors