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

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

  • wynhopkins's avatar
    wynhopkins
    Most Valuable Professional
    9 years ago

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