Using && in place of AND

[jasona]

Hello,

I have the below formula and it's not processing the last IF statement. I am getting no values back with a 1. Thank you in advance for the help.

Age Risk =
IF (
    'Pt Demo'[Patient Date of Birth - Copy] >= 75,
    4,
    IF (
        AND (
            'Pt Demo'[Patient Date of Birth - Copy] >= 60,
            'Pt Demo'[Patient Date of Birth - Copy] <= 74
        ),
        3,
        IF (
            AND (
                'Pt Demo'[Patient Date of Birth - Copy] >= 35,
                'Pt Demo'[Patient Date of Birth - Copy] <= 59
            ),
            2,
            IF ( 'Pt Demo'[Patient Date of Birth - Copy] <= 34, 1, 0 )
        )
    )
)

[Ashish_Mathur · Accepted solution]

Hi,

This can be simplified to:

IF('Pt Demo'[Patient Date of Birth - Copy] >= 75,4,IF('Pt Demo'[Patient Date of Birth - Copy]>=60,3,IF('Pt Demo'[Patient Date of Birth - Copy] >= 35,2,1)))

Hope this helps.

[v-rzhou-msft · Accepted solution]

Hi @jasona 

I think you build a calcualted column, though your calculated column is complex, it should be correct.

Here I build a sample table "Pt Demo" with [Patient Date of Birth - Copy] column from 1 to 100. And I try your code. It works well. Please check your data model, the last if is all values <= 34 will return 1.

The logic of your code is return 4 if [Patient Date of Birth - Copy] >= 75, if [Patient Date of Birth - Copy]>=60, return 3, if [Patient Date of Birth - Copy]>=35 return 2 and others([Patient Date of Birth - Copy]<=34) return 1. There is no range to return 0.

Ashish_Mathur has show you the sample code with if function. You can also try switch function to get your result.

Age Risk2 =
SWITCH (
    TRUE (),
    'Pt Demo'[Patient Date of Birth - Copy] >= 75, 4,
    'Pt Demo'[Patient Date of Birth - Copy] >= 60, 3,
    'Pt Demo'[Patient Date of Birth - Copy] >= 35, 2,
    1
)

Best Regards,

Rico Zhou

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

[Ashish_Mathur · Accepted solution]

Hi,

This can be simplified to:

IF('Pt Demo'[Patient Date of Birth - Copy] >= 75,4,IF('Pt Demo'[Patient Date of Birth - Copy]>=60,3,IF('Pt Demo'[Patient Date of Birth - Copy] >= 35,2,1)))

Hope this helps.