Forum Discussion

lc1's avatar
lc1
Helper III
7 years ago
Solved

If Statement

Hello,

I need to create the following formula in DAX:

excel:

if (column 1="C", column 5-column 7, "Open"

PBI:

Response Time = IF(sr[source]="c", calculate (sr[receiveddate]-sr[closingdate]),"Open"))

 

Please advise how to do it, I tried many different ways but I keep getting a syntax error (invalid token, line 1, offset 30, 'sr[receiveddate]-sr[closingdate]),"Open")))))

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    lc1 can you try follownig, I'm shooting in dark without looking at data model. 

     

    Response Time = IF(sr[source]="c", 
DATEDIFF(sr[receiveddate],sr[closingdate], DAY))

    Make sure to add as a column? And I assume datatype for both receivedate and closingdate is date.

8 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    lc1 try this

     

    Response Time = IF(sr[source]="c", 
DATEDIFF(sr[receiveddate],sr[closingdate], DAY),
"Open")
    • lc1's avatar
      lc1
      Helper III
      7 years ago

      I get this error:

       

      A single value for column 'receiveddate' in table 'sr' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

      • parry2k's avatar
        parry2k
        Super User
        7 years ago

        lc1 Are you adding this as a measure or column? The dax I proposed suppose to be a column.

         

        And you might run into another error, we are returning number but in else condition we are returning text, but let's see if we have add this expression as a column.