What is the fault in my formula ?

[RvdHeijden]

I want to make a formula that uses data from different colums within the same table.

Status Taak = IF(AND(Taken[PercentageVoltooidTaak]<>100;Taken[EinddatumTaak]<TODAY());"Te laat";IF(Taken[PercentageVoltooidTaak]=100;"Gesloten";IF(Taken[PercentageVoltooidTaak]<>100;Taken[EinddatumTaak]>TODAY();"Open")))

The Tasks have a Start and an End date and when they are done the task gets 100% so what i'm trying to do here is the same as in Excel but in Dax its a bit different and gives this error 

Expressions that yield variant data-type cannot be used to define calculated columns.

Can someone tell me what im doing wrong ?

[mattbrice]

The problem is you missed the "AND" function on the innermost IF statement.  Without it Dax detected the possibility your innermost IF could return a boolean data type along with the other text data types.  All values in a column have to be of the same datatype so it threw an error.  Excel has the "variant" data type which is not allowed in calculated columns.

I think you intended this:

Status Taak =
IF (
    AND ( Taken[PercentageVoltooidTaak] <> 100; Taken[EinddatumTaak] < TODAY () );
    "Te laat";
    IF (
        Taken[PercentageVoltooidTaak] = 100;
        "Gesloten";
        IF (
            AND ( Taken[PercentageVoltooidTaak] <> 100; Taken[EinddatumTaak] > TODAY () );
            "Open"
        )
    )
)

FWIW, I agree with @Phil_Seamark that his way is cleaner and easier to read.  Internally it gets rewritten as nested IF statements, but for you easier to deal with.

[Phil_Seamark]

Hi @RvdHeijden

Please try this approach.  You may need to tweak the rules but I hope you get the idea

Status Taak = 
    SWITCH(
            --- IF TRUE --
                True(),
            -- Tests ----
		Taken[PercentageVoltooidTaak]<>100 && Taken[EinddatumTaak]<TODAY() 	,"Te laat",
		Taken[PercentageVoltooidTaak]=100    ,"Gesloten",
		Taken[PercentageVoltooidTaak]<>100 && Taken[EinddatumTaak]>TODAY(),	"Open",
            -- ELSE ----
		"Other"
		)

[RvdHeijden]

@Phil_Seamark thanks for your help but we are not quite there yet 🙂

Status Taak = SWITCH(Taken[PercentageVoltooidTaak]<>100 && Taken[EinddatumTaak]<TODAY() ;"Te laat"; Taken[PercentageVoltooidTaak]=100 ;"Gesloten"; Taken[PercentageVoltooidTaak]<>100 && Taken[EinddatumTaak]>TODAY(); "Open";"Other" )

The formula still returns this error:

Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[mattbrice]

You missed the TRUE() statement @Phil_Seamark showed.

Status Taak =
SWITCH (
    TRUE ();
    Taken[PercentageVoltooidTaak] <> 100
        && Taken[EinddatumTaak] < TODAY (); "Te laat";
    Taken[PercentageVoltooidTaak] = 100; "Gesloten";
    Taken[PercentageVoltooidTaak] <> 100
        && Taken[EinddatumTaak] > TODAY (); "Open";
    "Other"
)

[mattbrice · Accepted solution]

Sorry to beat a dead horse, but considering statements are evaluated in order and evalutation stops at first TRUE() statement, you could shorten it up to this:

Status Taak =
SWITCH (
    TRUE ();
    Taken[PercentageVoltooidTaak] = 100; "Gesloten";
    Taken[EinddatumTaak] < TODAY (); "Te laat";
    Taken[EinddatumTaak] > TODAY (); "Open";
    "Other"
)

[RvdHeijden]

@Phil_Seamark your formula worked as a charm, thanks for the help

[v-ljerr-msft]

Hi @RvdHeijden,

Great to hear the problem got resolved! Could you accept helpful replies as solution to close this thread? 

Regards

[Phil_Seamark]

Oh sorry. What are the data types of the two columns in your table?

Is PercentageVoltooidTaak a number and not text?