Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to 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
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] )
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
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
@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
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] )
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] )
Hi @Zubair_Muhammad ,
Superb!! This query works excellently for my dataset..
Thanks for your contnious help 🙂
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
122 | |
109 | |
60 | |
55 |