Trying to transform an excel formula into Dax :)

[RvdHeijden]

Hello,

Im trying to transform my Excel formula into DAX but obviously it doesn't work.

Can someon help me in transforming this formula into DAX ??

Status Civiel = IF(Activiteit="Civiel";IF(taken[Gesloten)<>"";"Gesloten";IF(and(taken[Open]<>"";taken[gesloten]="";"Lopend";IF(taken[begin]<>"";"Gepland";"Nog niet gepland")))))

[Greg_Deckler]

Tough to sort through this, but I think you might want something like:

Status Civiel = 

IF(Activiteit="Civiel";
     IF(taken[Gesloten)<>"";
          "Gesloten";
          IF(and(taken[Open]<>"";taken[gesloten]="");
               "Lopend";
               IF(taken[begin]<>"";
                     "Gepland";
                     "Nog niet gepland"
                 )
            )
       )
  )

[RvdHeijden]

@Greg_Deckler

Something like that 🙂

it a conditional colum with basically a few options depending on values in a nother column

option 1: Activity A is closed = value A

option 2: Activity A is not closed but it's being worked on = value B

option 3: Activity A is not closed and not being worked, but is planned = value C

Option 4: Activity A is not closed, not worked on, not planned = value D

In excel its the formula i posted but in Dax  ????

your formula returns the following error. shouldn't this formula has a 'SWITCH' in it ?

[Greg_Deckler]

You have an issue with data types. You are trying to compare a Date with a text ("") and you probably need to use BLANK() in place of "" in one of those comparisons.

[RvdHeijden]

@Greg_Deckler

im not sure how to correct it, all datatype for dates colums have been checked and have the proper format.

The first question .....IF(Taken[Activiteit]="Civiel"..... is obviously a tekst format but then its checking datevalues

[Greg_Deckler · Accepted solution]

Which fields/columns are flagged as Date? For example, if "taken[Begin]" is a Date field, then you can't do a comparison to a text value of "", You would have to use "taken[Begin] <> BLANK()" instead.

[RvdHeijden]

@Greg_Deckler

The Colums Taken[Gesloten], [Geopend] and [Begin] are datecolumns

Ive change my formula so that "" becomes BLANK() but it now reads a syntax error

Status Civiel =
IF(Taken[activiteit]="Civiel";
     IF(taken[Gesloten]<>BLANK();
          "Gesloten";
          IF(and(taken[geopend]<>BLANK();taken[gesloten]=BLANK());
               "Lopend";
               IF(taken[begin]<>BLANK;
                     "Gepland";
                     "Nog niet gepland"
                 )
            )
       )
  )

[RvdHeijden]

@Greg_Deckler

Just i was posting my reply ive noticed one of the BLANK didnt have the () behind it.

I fixed it and now the formula works......

Status Civiel =
IF(Taken[activiteit]="Civiel";
     IF(taken[Gesloten]<>BLANK();
          "Gesloten";
          IF(and(taken[geopend]<>BLANK();taken[gesloten]=BLANK());
               "Lopend";
               IF(taken[begin]<>BLANK();
                     "Gepland";
                     "Nog niet gepland"
                 )
            )
       )
  )