cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jibran Regular Visitor
Regular Visitor

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."

Table.JPG

 

 

 

 

 

 

Many Thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

Re: IF statement not working

@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 4
Highlighted
deldersveld
Advisor

Re: IF statement not working

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 Regular Visitor
Regular Visitor

Re: IF statement not working

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")

 

duplicate data.JPG

My desired result has to be something like this

 

desired result.JPG

 

 

Many Thanks 

 

 

v-haibl-msft Super Contributor
Super Contributor

Re: IF statement not working

@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

wynhopkins Member
Member

Re: IF statement not working

Hi @jibran

 

I think you can achieve what you want with a measure rather than a calculated column

 

Extended Last Date =CALCULATE( LASTDATE(ExtendedContract[Ext End Contract]), 
ExtendedContract[VEHICLE STATUS]="Extended Contract")

 

Extended End Date (Last Date).PNG