don't calculate if ISBLANK

[adavid999]

Hi what is the best way to use ISBLANK to cover two measures? I.e. I want to leave result of [2019 sales] - [2018 sales] blank, or I don't want to calculate, if either 2019 or 2018 is blank. At the moment it is only working on 2019.

=IF ( ISBLANK ( [2019 sales]) , BLANK() , [2019 sales] - [2018 sales])

Thanks,

A

[v-alq-msft]

Hi, @adavid999 

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

Table:

You may try the following measures.

2018Sales = SUM('Table'[2018 Sales])

2019Sales = SUM('Table'[2019 Sales])

Result1 = 
IF(
    OR(
        ISBLANK([2018Sales]),
        ISBLANK([2019Sales])
    ),
    BLANK(),
    [2019Sales]-[2018Sales]
)

Result2 = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[ID],
    'Table'[2018 Sales],
    'Table'[2019 Sales],
    "Result",
    IF(
        OR(
            ISBLANK('Table'[2018 Sales]),
            ISBLANK('Table'[2019 Sales])
        ),
        BLANK(),
        'Table'[2019 Sales]-'Table'[2018 Sales]
    )
)
return
SUMX(
    tab,
    [Result]
)

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.

[aj1973]

Hi 

It is because your IF is filtering only on 2019.

Use your expression into a SUMX function.

Regards

[parry2k · Accepted solution]

@adavid999 in case of both, try this

=IF ( ISBLANK ( [2019 sales]) || ISBLANK ( [2018 Sales] ), BLANK() , [2019 sales] - [2018 sales])

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[adavid999]

This did the job, thank you.

A