Need help with condition for null

[Anonymous]

Hello friends, i have a column where are birth years of users, but there are also null values. I need to make a condition where i need to find if user is "older 50 years", "younder 50 years" or "cannot be calculated" due to null value. I tried something like this: 

Older or younger 50 years =

Switch ( True();

2020 - 'Data 2015, 2016'[birth year] > 50; "More then 50 years";

'Data 2015, 2016'[birth year] = null; "Cant calculate";

2020 - 'Data 2015, 2016'[birth year] < 50; "Younger 50 years")

But it cannot handle with null value. Can you help me how to make a condition for null value?

[az38 · Accepted solution]

@Anonymous 

hi

try

Older or younger 50 years =
Switch ( True();
2020 - 'Data 2015, 2016'[birth year] > 50; "More then 50 years";
ISBLANK('Data 2015, 2016'[birth year]); "Cant calculate";
2020 - 'Data 2015, 2016'[birth year] < 50; "Younger 50 years")

or, if your field return null as a text:

Older or younger 50 years =
Switch ( True();
2020 - 'Data 2015, 2016'[birth year] > 50; "More then 50 years";
'Data 2015, 2016'[birth year] = "null"; "Cant calculate";
2020 - 'Data 2015, 2016'[birth year] < 50; "Younger 50 years")

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.
Table:

You may try the following calculated columns.

Older or younger 50 years = 
SWITCH(
    TRUE(),
    'Table'[Age]>=50,"More than 50 years",
    'Table'[Age]<50&&'Table'[Age]>0,"Younger than 50 years",
    'Table'[Age]=BLANK(),"Cant calculate",
    BLANK()
)

or

Older or younger 50 years 2 = 
IF(
    'Table'[Age]>=50,
    "More then 50 years",
    IF(
        'Table'[Age]>0&&'Table'[Age]<50,
        "Younger than 50 years",
        IF(
            'Table'[Age]=BLANK(),
            "Cant calculate",
            BLANK()
        )
    )
)

Result:

Best Regards

Allan

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

[nandukrishnavs · Accepted solution]

@Anonymous 
Try this

Older or younger 50 years = 
var age= 2020- 'Data 2015'[birth year]
var result = IF(ISBLANK('Data 2015'[birth year]),"Cant calculate",IF(age>50,"Greater","less"))
return result

[az38 · Accepted solution]

@Anonymous 

hi

try

Older or younger 50 years =
Switch ( True();
2020 - 'Data 2015, 2016'[birth year] > 50; "More then 50 years";
ISBLANK('Data 2015, 2016'[birth year]); "Cant calculate";
2020 - 'Data 2015, 2016'[birth year] < 50; "Younger 50 years")

or, if your field return null as a text:

Older or younger 50 years =
Switch ( True();
2020 - 'Data 2015, 2016'[birth year] > 50; "More then 50 years";
'Data 2015, 2016'[birth year] = "null"; "Cant calculate";
2020 - 'Data 2015, 2016'[birth year] < 50; "Younger 50 years")