Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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

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

 


Regards
Zubair

Please try my custom visuals

View solution in original post

10 REPLIES 10
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Hello @Zubair_Muhammad ,

 

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

 

@Anonymous 

 

Here it goes


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

@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


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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.

 

 

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

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Hi @Zubair_Muhammad ,

 

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

Thanks for your contnious help 🙂

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.