Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Expression that yield variant data type error cannot be used to defined calculated columns

Hi,   * Im new to power bi and moving from tableau backgroud. I have a calculation, desribing that in words(nested if)   if date_col is not null then       if(date_col) < today then display tod...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    You can use this MEASURE

    Please see attached file

     

    Measure =
VAR result =
    MIN ( 'handle blanks'[Estimated Delivery Date] )
RETURN
    IF ( ISBLANK ( result ), "Data Unavailable", FORMAT ( result, "Short date" ) )

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Zubair,

 

Not sure if i understood correctly, but here is what my calculation within your formula is

 

Estimated Delivery Date New =

var estimated_delivery_Date = if(baseline_iv[est_deliv_dt]<TODAY(),TODAY(),baseline_iv[est_deliv_dt])

return if(estimated_delivery_Date=BLANK(),"Data Unavailabe",estimated_delivery_Date)

 

Let me know if this is correct. Also please explain the same.

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

HI Anonymous

 

Try this

 

Basically I am converting Date to Text Format by adding a  " "  at the end of formula



Estimated Delivery Date New =
VAR estimated_delivery_Date =
    IF ( baseline_iv[est_deliv_dt] < TODAY (), TODAY (), baseline_iv[est_deliv_dt] )
RETURN
    IF (
        estimated_delivery_Date = BLANK (),
        "Data Unavailabe",
        estimated_delivery_Date & ""
    )

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Zubari,

     

    This isnt working sadly.

    Can you suggest something else.

     

    Just FYI : I can have multiple dates for one location type so im using latest date there.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Anonymous

       

      Could you copy paste some data with expected results?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Let me know if you are not able to access this link