cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pushkard5 Regular Visitor
Regular Visitor

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Shortlist only continuous data points in a column

@pushkard5 

 

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 10
Super User
Super User

Re: Shortlist only continuous data points in a column

@pushkard5 

 

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]
    )
pushkard5 Regular Visitor
Regular Visitor

Re: Shortlist only continuous data points in a column

Hello @Zubair_Muhammad ,

 

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

 

Super User
Super User

Re: Shortlist only continuous data points in a column

@pushkard5 

 

Here it goes

pushkard5 Regular Visitor
Regular Visitor

Re: Shortlist only continuous data points in a column

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

Capture1.PNG

Super User
Super User

Re: Shortlist only continuous data points in a column

@pushkard5 

 

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

pushkard5 Regular Visitor
Regular Visitor

Re: Shortlist only continuous data points in a column

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

Super User
Super User

Re: Shortlist only continuous data points in a column

@pushkard5 

 

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]
    )
pushkard5 Regular Visitor
Regular Visitor

Re: Shortlist only continuous data points in a column

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.

 

 

Super User
Super User

Re: Shortlist only continuous data points in a column

@pushkard5 

 

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