Forum Discussion

seanrm42's avatar
seanrm42
Frequent Visitor
8 years ago
Solved

Null Value Boolean Correction for Software Analysis

I have the following tables linked together by application name:

 

Application Name:         Family:         Verssion: 

App9                              Fam1            9

App8                              Fam1            8              

App7                              Fam1            7                

App6                              Fam1            6                  

App5                              Fam1            5                   

App4                              Fam1            4                   

App3                              Fam1            3                              

 

Application Name:         Support End Date:        Support Ended?:

App9                                                                    FALSE     

App8                              1/1/2015                       TRUE     

App7                              1/1/2015                       TRUE   

App6                              1/1/2015                       TRUE   

App5                                                                    FALSE   

App4                                                                    FALSE  

App3                                                                    FALSE

 

The Support Ended column is a added custom column. The application family is the grouping for which the applications are segemented in to. 

 

The Support End Date column is null for some of the older applications which is turning the boolean column in to false negatives.

 

The boolean formula is calculating if the support end date is not null and before today then TRUE.

 

What can I add in to the formula or column can I create to change the boolean to TRUE if the application has another application within the family of a higher version with a support end date before today?

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago

    seanrm42

    You can try to create a calculated column as 

    Support Ended? =
VAR last_app_ver_SE_date_is_not_null =
    MINX (
        FILTER (
            Table2,
            EARLIER ( RELATED ( Table1[Version] ) ) < RELATED ( Table1[Version] )
                && RELATED ( Table1[Family] ) = EARLIER ( RELATED ( Table1[Family] ) )
                && NOT ( ISBLANK ( Table2[Support End Date] ) )
                && Table2[Support End Date] < TODAY ()
        ),
        RELATED ( Table1[Version] )
    )
RETURN
    SWITCH (
        TRUE (),
        NOT ( ISBLANK ( Table2[Support End Date] ) )
            && Table2[Support End Date] < TODAY ()
            || NOT ( ISBLANK ( last_app_ver_SE_date_is_not_null ) ), TRUE (),
        FALSE ()
    )

3 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    8 years ago

    seanrm42

    You can try to create a calculated column as 

    Support Ended? =
VAR last_app_ver_SE_date_is_not_null =
    MINX (
        FILTER (
            Table2,
            EARLIER ( RELATED ( Table1[Version] ) ) < RELATED ( Table1[Version] )
                && RELATED ( Table1[Family] ) = EARLIER ( RELATED ( Table1[Family] ) )
                && NOT ( ISBLANK ( Table2[Support End Date] ) )
                && Table2[Support End Date] < TODAY ()
        ),
        RELATED ( Table1[Version] )
    )
RETURN
    SWITCH (
        TRUE (),
        NOT ( ISBLANK ( Table2[Support End Date] ) )
            && Table2[Support End Date] < TODAY ()
            || NOT ( ISBLANK ( last_app_ver_SE_date_is_not_null ) ), TRUE (),
        FALSE ()
    )

    • seanrm42's avatar
      seanrm42
      Frequent Visitor
      8 years ago

      I cannot tell you how appreciative and amazed I am that you contructed that quickly.

       

      Many thanks from me to you and this community.