Forum Discussion

jibran's avatar
jibran
Helper II
9 years ago
Solved

IF statement not working

Hi All,

I am struggling to make work IF statement. 

I want to use IF statement where the value equals to the some value then show me the date value, else NULL value.

 

Example Ext End Contract = IF('Extended Contract'[VEHICLESTATUS] = "Extended Contract",LASTDATE('Extende
d Contract'[Ext End Contract]),"Jibran")

Error that I receive is "Expressions that yield variant data-type cannot be used to define calculated columns."

 

 

 

 

 

 

Many Thanks

 

Dashboards
Data Modeling
Reports
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    jibran

     

    Please try with following formula.

     

    Example Ext End Contract =
IF (
    'Extended Contract'[VEHICLESTATUS] = "Extended Contract",
    FORMAT (
        CALCULATE (
            LASTDATE ( 'Extended Contract'[Ext End Contract] ),
            ALLEXCEPT (
                'Extended Contract',
                'Extended Contract'[CUSTOMERNAME],
                'Extended Contract'[REGISTRATIONNO],
                'Extended Contract'[Fleetware Contract ID]
            )
        ),
        ""
    ),
    "Jibran"
)

     

    Best Regards,
    Herbert

4 Replies

  • deldersveld's avatar
    deldersveld
    Resident Rockstar
    9 years ago

    Your result cannot mix data types between Date and Text. If your alternate result is "Jibran" (Text), the first result must also be Text. You can use FORMAT() to convert the date value to text: FORMAT(LASTDATE( [...] ),"Text")

    • jibran's avatar
      jibran
      Helper II
      9 years ago

      Hi,

       

      I applied the Fomat function and pass the desired string to the function as shown below, but it is displaying duplicate data.

      It looks like with the inclusion of Format function the last date fuction is not working any more

       

      Dax query is

       

      Example Ext End Contract = IF('Extended Contract'[VEHICLESTATUS] = "Extended Contract",FORMAT(LASTDATE('Extended Contract'[Ext End Contract]), "ddd dd/MM/YYYY"),"No Date")

       

      My desired result has to be something like this

       

       

       

      Many Thanks 

       

       

      • v-haibl-msft's avatar
        v-haibl-msft
        Microsoft Employee
        9 years ago

        jibran

         

        Please try with following formula.

         

        Example Ext End Contract =
IF (
    'Extended Contract'[VEHICLESTATUS] = "Extended Contract",
    FORMAT (
        CALCULATE (
            LASTDATE ( 'Extended Contract'[Ext End Contract] ),
            ALLEXCEPT (
                'Extended Contract',
                'Extended Contract'[CUSTOMERNAME],
                'Extended Contract'[REGISTRATIONNO],
                'Extended Contract'[Fleetware Contract ID]
            )
        ),
        ""
    ),
    "Jibran"
)

         

        Best Regards,
        Herbert