Error with a IF formula dax

[Anonymous]

Hello. My table multiple columns but only these three interest for my question:

Order_Number || Picking_Date || Product_State

If a customer orders more than one product, the order_number appears repeated like these:

Order_Number || Picking_Date || Product_State

700200                 XX/XX/XXXX     Picked

700200                                          Canceled

I don't have the picking date from some orders so I want to, for the entries that Picking_date is blank and the product_state is "canceled", assume the first picking date for the same order_number if the same order has more than one product entry. For that I have the following expression:

=IF(Query_[Picking_date]=BLANK() && Query_[Product_state]="Canceled";IF(HASONEVALUE(Query_[Order_Number]);FIRSTDATE(Query_[Picking_date]);"without entries");Query_[Picking_date]).

But returns this error: Expressions that yield variant data-type cannot be used to define calculated columns.

My picking_date has date format, my order_number has whole number format and Product state has text format.

Thank you.

[Fowmy · Accepted solution]

@Anonymous 

You cannot mix two data types in a single column, you have got dates and text.

Try the following code to add a new column

M = 
VAR _ORDER = Query[Order_Number]
RETURN
IF(
    ISBLANK(Query[Picking_Date]) && Query[Product_State]="Cancelled",        

    var _date =  CALCULATE(MAX(Query[Picking_Date]), Query[Order_Number] = _ORDER, ALL(Query)) 
    var _dateformated =_date return
    IF( ISBLANK(_date), BLANK() , _dateformated),
        
    Query[Picking_Date]
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[AntrikshSharma]

@Anonymous  Try something like this:

Column =
VAR CurrentOrderNumber = jmsm[Order_Number]
VAR CurrentPickingDate = jmsm[Picking_Date]
VAR CurrentProductState = jmsm[Product_State]
VAR MaxDate =
    MAXX (
        FILTER (
            jmsm,
            jmsm[Order_Number] = CurrentOrderNumber
                && jmsm[Product_State] <> "Canceled"
                && NOT ISBLANK ( jmsm[Picking_Date] )
        ),
        jmsm[Picking_Date]
    )
VAR Result =
    IF (
        ISBLANK ( CurrentPickingDate )
            && CurrentProductState = "Canceled",
        MaxDate
    )
RETURN
    Result