Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Shortlist only continuous data points in a column

Hello All,

I want to create a column which returns the same values of data if they are contnious in nature.

 

For Example:- If there are 9 or more continious datapoints in sequence then, i should get the value of continious data points in a new column.

 

I am having below data set-

(Please note :- The Dates are non-continious )

 

(key)         (date)          (value )        (expected result)

 

s001visc   1jan18            20

s001visc   2jan18            19

s001visc   3jan18          

s001visc   4jan18             14

s001visc   5jan18             22

s001visc   9jan18             23

s001visc   11jan18           24

s001visc   13jan18          

s001visc   15jan18           32                   32

s001visc   18jan18           12                   12

s001visc   21jan18           05                  05

s001visc   22jan18           11                  11

s001visc   23jan18          15                   15

s001visc   24jan18          100               100

s001visc   25jan18           02                  02

s001visc   26jan18           15                 15

s001visc   29jan18           14                14

s001visc   30jan18           20               20

 

Is it possible to create a dax formula to return the above result in new column.

Please help.

 

 

Need Help
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous 

     

    To do the shortlisting for each key, we can do like this.

    I added another key to test. Please see file attached and see the calculated column

     

    Column =
VAR BLANKROWDatebefore =
    MINX (
        TOPN (
            1,
            FILTER (
                Table1,
                [key] = EARLIER ( [key] )
                    && [date] < EARLIER ( [date] )
                    && [value] = 0
            ),
            [Date], DESC
        ),
        [date]
    )
VAR BLANKROWDateafter =
    MINX (
        TOPN (
            1,
            FILTER (
                Table1,
                [key] = EARLIER ( [key] )
                    && [date] > EARLIER ( [date] )
                    && [value] = 0
            ),
            [Date], ASC
        ),
        [date]
    )
VAR Date1 =
    IF ( ISBLANK ( BLANKROWDatebefore ), DATE ( 1900, 1, 1 ), BLANKROWDatebefore )
VAR Date2 =
    IF ( ISBLANK ( BLANKROWDateafter ), DATE ( 3000, 1, 1 ), BLANKROWDateafter )
RETURN
    IF (
        [value] <> 0
            && COUNTROWS (
                FILTER ( Table1, [key] = EARLIER ( [key] ) && [date] < Date2 && [date] > Date1 )
            ) > 9,
        [value]
    )

     

10 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Anonymous 

     

    Try this. it works with sample date

     

    Column =
VAR BLANKROWDatebefore =
    MINX (
        TOPN (
            1,
            FILTER ( Table1, [date] < EARLIER ( [date] ) && ISBLANK ( [value] ) ),
            [Date], DESC
        ),
        [date]
    )
VAR BLANKROWDateafter =
    MINX (
        TOPN (
            1,
            FILTER ( Table1, [date] > EARLIER ( [date] ) && ISBLANK ( [value] ) ),
            [Date], ASC
        ),
        [date]
    )
VAR Date1 =
    IF ( ISBLANK ( BLANKROWDatebefore ), DATE ( 1900, 1, 1 ), BLANKROWDatebefore )
VAR Date2 =
    IF ( ISBLANK ( BLANKROWDateafter ), DATE ( 3000, 1, 1 ), BLANKROWDateafter )
RETURN
    IF (
        [value] <> BLANK ()
            && COUNTROWS ( FILTER ( Table1, [date] < Date2 && [date] > Date1 ) ) > 9,
        [value]
    )