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

[Anonymous]

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 today

      else  display minimum(date_col)

else display Data Unavailable.

Whenever i try to write if statements or switches i get the error mentioned in the subject line.

Any help would be highly appreciated.

[Zubair_Muhammad]

@Anonymous

What is your DAX formula

Try with this. Input your DAX calculated column formula in the _____

Calculated Column =
VAR yourformula = ____
RETURN
    IF ( yourformula = BLANK (), "Not Available", yourformula & "" )

[Anonymous]

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]

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]

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]

@Anonymous

Could you copy paste some data with expected results?

[Anonymous]

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

[Zubair_Muhammad]

@Anonymous

In the Excel file, could you add a column for expected result?

[Anonymous]

Hi Zubair,

I have added the expected result in pbix file where i have written the calculation as well in algorithmic form.

if you could see blanks in the pbix files for dates, those have to be replaced with 'data unavailable'

[Zubair_Muhammad · Accepted solution]

@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]

Hi Zubair,

I have another issue on the same view at 

https://community.powerbi.com/t5/Desktop/Displaying-totals-above-a-bar-chart-which-has-legends/m-p/4...

Could you please suggest somthing there?

[Anonymous]

 Its hard to type cast in powerbi. Thanku so much. I appreciate it