Need help correcting my Formula

[RvdHeijden]

Hello,

Ive got a formula that needs some work, there are a lot of IF's in

Status Schouw = IF(OR(Adressen[R.N.A.]="R40";(Adressen[R.N.A.]="R42"));"";
    IF(Adressen[Civiel geplande datum]=BLANK();"Civiel nog niet gepland";
        IF(Adressen[schouwen begin]=BLANK();"Schouw nog niet gepland";
            IF(OR(Adressen[schouwen gesloten]=BLANK();(Adressen[schouwen begin]<>BLANK()));"Bulkplanning";
                IF(OR(Adressen[schouwen gesloten]=BLANK();(Adressen[Schouw datum]<NOW()));"Te laat";
                    IF(Adressen[schouwen gesloten]<>BLANK();"Gesloten";
                        IF(MID(Adressen[schouwen begin];14;18)<>"00:00";"Vaste afspraak";
                            IF(SUMX(adressen;Adressen[Civiel geplande datum]-NOW())<21;"Te Laat";""))))))))

DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

I believe the error is caused in the last line because he needs to calculate the number of days between 2 colums and if its less then 21 days it should return the value 'Te laat'

[danextian]

hi @RvdHeijden,

Your formula seems fine but you could display it a in a neater way using Daxformatter.com

Status Schouw =
IF (
    OR ( Adressen[R.N.A.] = "R40"; ( Adressen[R.N.A.] = "R42" ) );
    "";
    IF (
        Adressen[Civiel geplande datum] = BLANK ();
        "Civiel nog niet gepland";
        IF (
            Adressen[schouwen begin] = BLANK ();
            "Schouw nog niet gepland";
            IF (
                OR (
                    Adressen[schouwen gesloten] = BLANK ();
                    ( Adressen[schouwen begin] <> BLANK () )
                );
                "Bulkplanning";
                IF (
                    OR (
                        Adressen[schouwen gesloten] = BLANK ();
                        ( Adressen[Schouw datum] < NOW () )
                    );
                    "Te laat";
                    IF (
                        Adressen[schouwen gesloten] <> BLANK ();
                        "Gesloten";
                        IF (
                            MID ( Adressen[schouwen begin]; 14; 18 ) <> "00:00";
                            "Vaste afspraak";
                            IF (
                                SUMX ( adressen; Adressen[Civiel geplande datum] - NOW () )
                                    < 21;
                                "Te Laat";
                                ""
                            )
                        )
                    )
                )
            )
        )
    )
)

[RvdHeijden]

@danextian@v-yuezhe-msft

Ive changed a few date colums because 2  were indeed referring to a tekst value.

They looked like dates but they were the outcome of a formula and the data type was set to 'tekst'.

So problem solved....thanks 🙂

[v-yuezhe-msft · Accepted solution]

@RvdHeijden,

There is no issue with the last line when I make a test using sample data in my table. Could you please check that if you change the data type of Schouw datum field to Date? If this field is a text field, the above error will occur because you are comparing text values with date values in the following part. Also ensure that you have defined the data type of the Civiel geplande datum field to Date.

Adressen[Schouw datum]<NOW()

If the above step doesn't help, please share sample data of your table for us to test.

Regards,
Lydia