cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

1 ACCEPTED SOLUTION
Community Champion

@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

10 REPLIES 10
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

Anonymous
Not applicable

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

Community Champion

@Anonymous

Here it goes

Regards
Zubair

Anonymous
Not applicable

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

Community Champion

@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

Anonymous
Not applicable

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?

Community Champion

@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

Anonymous
Not applicable

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.

Community Champion

@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

Anonymous
Not applicable

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

Thanks for your contnious help 🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.