Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Help in nested if statement in a new column

Hi all,

I am new to Power BI and want to write a calculation using following columns

Quantity on hand , Po on order and YTD average sales and  Shipping Date  which are direct column from table.

The calculation should work like

If shipping date in data set is not null, then calculation should be:

  • Cal=Quantity on hand+ Current order/YTD average sales

Else the calculation should be:

  • Cal=Quantity on hand+ /YTD average sales

Also if Quantity on hand, Po on order and YTD average sales is null then Cal=0

I am unable to get null function in this calculation. 

Any suggestions what am i missing.

Need Help
Tips and Tricks
  • Arul's avatar
    Arul
    3 years ago

    Anonymous ,

    check if this code helps or not,

    Cal = 
SWITCH (
    TRUE (),
    'Table'[Shipping Date] <> BLANK (), 'Table'[Quantity on hand] + 'Table'[YTD average sales],
    'Table'[Quantity on hand] = BLANK ()
        || 'Table'[Po on order] = BLANK ()
        || 'Table'[YTD average sales] = BLANK (), 0,
    'Table'[Quantity on hand] + 'Table'[YTD average sales]
)

    Thanks,

    Arul

     

1 Reply

  • Arul's avatar
    Arul
    Super User
    3 years ago

    Anonymous ,

    check if this code helps or not,

    Cal = 
SWITCH (
    TRUE (),
    'Table'[Shipping Date] <> BLANK (), 'Table'[Quantity on hand] + 'Table'[YTD average sales],
    'Table'[Quantity on hand] = BLANK ()
        || 'Table'[Po on order] = BLANK ()
        || 'Table'[YTD average sales] = BLANK (), 0,
    'Table'[Quantity on hand] + 'Table'[YTD average sales]
)

    Thanks,

    Arul