Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

IF Statement Not Working

Hey BI Community,

 

It has been a while since I have been on for some help. I am looking for some today though. I have built an IF Statment to add text indicators in one column related to whole number values in another. What I am trying to do is label certain groups of numbers based on certain criteria. However, when I look at the table for some reason it comes out incorrectly, with numbers not fitting appropriately. I will include the table image below and also the associated functions. 

 

 

 

So in the image, there is a column title DayDiff_OpenPO this column takes the DateDiff of Delivery Date & Confirmed Delivery Date and returns a value. Then, based on that value the column "PO Forecast Label" should say Early, On-Time, or Late depending on the value. 

 

Date diff function: 

 

DayDiff_OpenPO = DATEDIFF(OnTimeDelivery[Delivery Date],OnTimeDelivery[ConfirmedDeliveryDate],DAY)
 
IF Statement: 
 
PO Forecast Label = IF(OnTimeDelivery[DayDiff_OpenPO] <= -5 , "Expected Early", IF(AND(OnTimeDelivery[DayDiff_OpenPO] >= -4, OnTimeDelivery[DayDiff_Requested] <= 3), "Expected On-Time", IF(OnTimeDelivery[DayDiff_OpenPO] >= 4, "Expected Late", BLANK())))
 
I am seriously unsure why it is coming out this way as I have not had this problem before. Any help would be greatly appreciated!
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Seem correct hope both are columns ??

    You can try line -

    PO Forecast Label =
    switch (True(),
    OnTimeDelivery[DayDiff_OpenPO] <= -5 , "Expected Early",
    OnTimeDelivery[DayDiff_OpenPO] >= -4 && OnTimeDelivery[DayDiff_Requested] <= 3, "Expected On-Time",
    OnTimeDelivery[DayDiff_OpenPO] >= 4, "Expected Late"
    ,blank())

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

    hi  Anonymous 

    It seems that [PO Forecast Label] is a new column? 

    For your case, you should create a new measure

     

    PO Forecast Label = IF(SUM(OnTimeDelivery[DayDiff_OpenPO]) <= -5 , "Expected Early", IF(AND(SUM(OnTimeDelivery[DayDiff_OpenPO]) >= -4, SUM(OnTimeDelivery[DayDiff_Requested]) <= 3), "Expected On-Time", IF(SUM(OnTimeDelivery[DayDiff_OpenPO]) >= 4, "Expected Late", BLANK())))

     

    iF not your case, please share your sample pbix file for us have a test, that will be a great help.

     

    Regards,

    Lin