Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help with condition for null

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?
Need Help
  • az38's avatar
    az38
    6 years ago

    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")
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    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

     

4 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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")
  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    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

     

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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