Shortlist only continuous data points in a column

[Anonymous]

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.

[Zubair_Muhammad]

@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]
    )

[Anonymous]

Hello @Zubair_Muhammad ,

Thanks for the solution. Can you share the pbix file as the solution is not working at my end.

[Zubair_Muhammad]

@Anonymous 

Here it goes

[Anonymous]

Hello @Zubair_Muhammad ,

Thank you once again for the file. I don't know why but the formula is not working for my data. I am getting all the points again same and the count criteria is not working. Can we also include the key column in the formula as it is playing important role in my data.

Can you help on same?

I have attached the screenshot for your reference.

Best Regards,

Pushkar

[Zubair_Muhammad]

@Anonymous 

Seems like you have zeros in the data while I assumed these are blanks.

Could you try changing BLANK() with zero in the formula

You can also share your file with me if you like

[Anonymous]

@Zubair_Muhammad ,

I am sorry. I cannot share the data file. I have replaced the blanks in data by zeros. Can you guide me with the further step or the show the change in formula?

[Zubair_Muhammad]

@Anonymous 

Here is the revision

File attached as well

Column =
VAR BLANKROWDatebefore =
    MINX (
        TOPN (
            1,
            FILTER ( Table1, [date] < EARLIER ( [date] ) && [value] = 0 ),
            [Date], DESC
        ),
        [date]
    )
VAR BLANKROWDateafter =
    MINX (
        TOPN (
            1,
            FILTER ( Table1, [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, [date] < Date2 && [date] > Date1 ) ) > 9,
        [value]
    )

[Anonymous]

Hi @Zubair_Muhammad ,

I have tried the above method but still I am not getting the expected result. 

Can we include the key in the formula as well? 

Becasue my dataset can be uniqly identified on the basis of key.

[Zubair_Muhammad · Accepted solution]

@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]
    )

[Anonymous]

Hi @Zubair_Muhammad ,

Superb!! This query works excellently for my dataset..

Thanks for your contnious help 🙂