Blank rows resulting into error for If Then and Else statement

[Anonymous]

Hello,

Could someone please advise as I keep getting error for blank rows (column heading shows a Red underline whereas other formula containing column heading show green underline)

Error : [Expression.Error] We cannot convert the value null to type Logical.

I am using a formula as below

if [Status] <> "" and [Close date] < #"Period Start" or #"Period End" < [Open date] then "Hide" else "Show"

It was working fine in tableau but in power bi may be I am missing something to fix the problem for cells containing null values.

Status column for each row either contains Active or Not active

Appreciate any guidance to fix the if else error.

thanks

[Anonymous]

@dharmendars007 @Anonymous 

I am looking to evaluate the existing column Close date with the parameter Period start. In tableau, you can use columns + parameters in the formula and execute the calculations such as in this case the count the tasks completion using number of "show" rows..

Thanks

[Anonymous]

hi - @Anonymous 

Thanks for reply with detailed steps, please note that the words "Period Start" are actually parameters that I have created to define the start of the period and likewise "Period End" for end of the period.

I am still having the below error 

Column 'Period Start' cannot be found or may not be used in this expression.

[dharmendars007]

Hello @Anonymous , 

Please find the modified logic to hendle the status column is null or blank..

IF(
NOT(ISBLANK([Status])) &&
NOT(ISBLANK([Close date])) &&
([Close date] < #"Period Start" || #"Period End" < [Open date]),"Hide","Show")

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Anonymous]

Hi @dharmendars007 

Thanks,I am getting this error when trying to type the parameter names such as #'Period start' above.

Error: The following syntax error occurred during parsing: Invalid token, Line 1, Offset 107, #.

how do I get the parameters in this calculated column?

[Anonymous]

Thanks for the reply from dharmendars007.

Hi @Anonymous ,

The error is shown in the following image:

In your formula, using the # symbol can lead to syntax errors. You can refer to these column names directly using  [    ]:

column = IF(
NOT(ISBLANK([Status])) &&
NOT(ISBLANK([Close date])) &&
([Close date] < [Period Start] || [Period End] < [Open date]),"Hide","Show")

You can follow the link below to learn:DAX syntax - DAX | Microsoft Learn

Best Regards,
Zhu
Community Support Team

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