Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RvdHeijden
Post Prodigy
Post Prodigy

Need help correcting my Formula

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'

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
danextian
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

@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
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.