Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ...
Need Help
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

Hello Zubair_Muhammad ,

 

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Zubair_Muhammad
      Community Champion
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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?