Forum Discussion

klb0066's avatar
klb0066
Frequent Visitor
8 years ago

if statement

Creating a splicer with title "Has Due Date?" with True or False checkboxes. Coded an if statement that recognizes if field was left bank then it is false and if the field has a date it is true.

 

hasDue = if(ISBLANK(MX_TICKET[TARGETFINISH]),FALSE(),TRUE())

 

Would like to add an overdue option. How do I add this to my statement and link current date to due date then identify if it overdue?

6 Replies

  • Smauro's avatar
    Smauro
    Solution Sage
    8 years ago

    Hey klb0066

     

    I'm guessing [hasDue] is a column, so, you could either a) create a new column and refresh the model every day or b) have a measure and have a table visual with what you need.

    a) 

    isDue = IF ( [hasDue] && [TARGETFINISH] <= TODAY() , TRUE(), FALSE() )

    Which checks if your [hasDue] is true and the finish target was before or today. One thing you may want to check with your data is whether you have items which are already finished in your table. If so you'll need another clause like NOT([Completed]) or [CompletedDate]=BLANK()

    b) 

    isDue =
VAR d =
    SELECTEDVALUE ( MX_TICKET[hasDue] )
VAR tf =
    SELECTEDVALUE ( MX_TICKET[TARGETFINISH] )
RETURN
    IF ( d && tf <= TODAY (), TRUE (), FALSE () )

    Which does the same as previous, but the table visual should contain a table key (meaning no row is aggregated)

     

     

     

     

  • klb0066's avatar
    klb0066
    Frequent Visitor
    8 years ago

    Created this statement for a splicer

     

    hasDue = if(ISBLANK(MX_TICKET[TARGETFINISH]),FALSE(),TRUE())

     

    Identifies blank entries as false and entries that have a date as true. I want to add and overdue checkbox. How do I relate current date to due dates then pull out over due pieces?

    • klb0066's avatar
      klb0066
      Frequent Visitor
      8 years ago

      It is for work and you are allowed to enter the due date or enter no due date (we are changing this procedure as entering in no due date is pointless now). I want to be able to see what work is overdue that has a due date.

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        8 years ago

        Hi klb0066

        What do you mean by "you are allowed to enter the due date or enter no due date"?

        If your current date is a column which exsits in the table, you can use the following formula

        create calculated column, then add this column to a slicer

        hasDue = if(ISBLANK(MX_TICKET[TARGETFINISH]),FALSE(),TRUE())

        cretae a measure, then add this measure to the Visual Filter

        if over due = IF(MAX([TARGETFINISH])>MAX([current date]),1,0)

         

        If the current date refer to today, you can use TODAY( ) to replace [current date].

         

        Best Regards

        Maggie

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    Hi klb0066

    Create a calculated column

    overdue = IF(ISBLANK([target]),BLANK(),IF([date]>[target],1,0) )

     

    Best Regards

    Maggie