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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hwoehler
Helper I
Helper I

DAX-count consecutive identical values (ring dependency error)

Hi everybody,

I would like to count consecutive identical values. There are several symbols (S, T, K, ...) for which the longest positive and negative series (Value1) should be displayed/visualized. Say, if Value1 is positive for several consecutive days, the Result-Value should always increase by one, to find out later the maximum for the longest series. I have already tried it with a column Value2, which display only 1 or 0 for positive and negatie values. I don't have any other ideas to solve this.

 

I have already been able to find a solution (https://community.powerbi.com/t5/Desktop/DAX-how-to-count-consecutive-identical-values/m-p/701936#M3...) for it, but (for my special case) this only worked with a table where only values ​​for a symbol are in it. For several symbols (T, S, K ..) in a table, only the error: Ring dependency is displayed.

 

In my complete file, there is a DateTable, a table with only the Symbols ("Symbol") to filter and a Table (named "All") as below, which have relationships.

Data set ("All") example:

Symbol            Date            Value1             Value2              Result (1)        Result (0)

S                01/10/2018      13,20                  1                       1                     0

S                01/09/2018      33,10                  1                       2                     0

S                01/08/2018      -12,10                 0                      0                     1

S                01/07/2018      -14,10                 0                      0                     2

T                01/10/2018      -15,10                 0                      0                     1

T                01/09/2018      12,05                  1                       1                     0

T                01/08/2018      24,00                  1                       2                     0
T                01/07/2018      23,00                  1                       3                     0

K                01/10/2018      5,12                    1                       1                    0

K                01/09/2018      -3,23                  0                       0                     1

K                01/08/2018      -55,32                0                       0                     2

 

I am thankful for every help.

hwoehler

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi, @hwoehler 

First, from your sample data, if the date should be as below:

 

Just add the conditional as below:

Expected resilt(1) = 
VAR _date =
    CALCULATE (
        MAX(  'Table'[Date] ),
        FILTER (
            CALCULATETABLE ( 'Table', ALLEXCEPT(  'Table','Table'[Symbol]), 'Table'[Value2] = 0 ),
            'Table'[Date] <= EARLIER ( 'Table'[Date] )
        )
    )
RETURN
  CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table','Table'[Symbol]=EARLIER('Table'[Symbol])&&
            'Table'[Date]<= EARLIER ( 'Table'[Date] )
                && 'Table'[Date] > _date
        )
    ) +0
Expected resilt(0) = 
VAR _date =
    CALCULATE (
        MAX(  'Table'[Date] ),
        FILTER (
            CALCULATETABLE ( 'Table', ALLEXCEPT(  'Table','Table'[Symbol]), 'Table'[Value2] = 1 ),
            'Table'[Date] <= EARLIER ( 'Table'[Date] )
        )
    )
RETURN
  CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table','Table'[Symbol]=EARLIER('Table'[Symbol])&&
            'Table'[Date]<= EARLIER ( 'Table'[Date] )
                && 'Table'[Date] > _date
        )
    ) +0

Result:

7.JPG

and if your want the date descending order, just add the way as below:

Add an index column for date by Symbol       

Date Index = RANKX(FILTER('Table 2','Table 2'[Symbol]=EARLIER('Table 2'[Symbol])),'Table 2'[Date],,DESC)

Then adjust formula as below:

new Expected resilt(1) = 
VAR _date =
    CALCULATE (
        MAX(  'Table 2'[Date Index] ),
        FILTER (
            CALCULATETABLE ( 'Table 2', ALLEXCEPT(  'Table 2','Table 2'[Symbol]), 'Table 2'[Value2] = 0 ),
            'Table 2'[Date Index] <= EARLIER ( 'Table 2'[Date Index] )
        )
    )
RETURN
  CALCULATE (
        COUNTROWS ( 'Table 2' ),
        FILTER (
            'Table 2','Table 2'[Symbol]=EARLIER('Table 2'[Symbol])&&
            'Table 2'[Date Index]<= EARLIER ( 'Table 2'[Date Index] )
                && 'Table 2'[Date Index] > _date
        )
    ) +0
new Expected resilt(0) = 
VAR _date =
    CALCULATE (
        MAX(  'Table 2'[Date Index] ),
        FILTER (
            CALCULATETABLE ( 'Table 2', ALLEXCEPT(  'Table 2','Table 2'[Symbol]), 'Table 2'[Value2] = 1),
            'Table 2'[Date Index] <= EARLIER ( 'Table 2'[Date Index] )
        )
    )
RETURN
  CALCULATE (
        COUNTROWS ( 'Table 2' ),
        FILTER (
            'Table 2','Table 2'[Symbol]=EARLIER('Table 2'[Symbol])&&
            'Table 2'[Date Index]<= EARLIER ( 'Table 2'[Date Index] )
                && 'Table 2'[Date Index] > _date
        )
    ) +0

 

Result:
9.JPG

 

and here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

hi, @hwoehler 

First, from your sample data, if the date should be as below:

 

Just add the conditional as below:

Expected resilt(1) = 
VAR _date =
    CALCULATE (
        MAX(  'Table'[Date] ),
        FILTER (
            CALCULATETABLE ( 'Table', ALLEXCEPT(  'Table','Table'[Symbol]), 'Table'[Value2] = 0 ),
            'Table'[Date] <= EARLIER ( 'Table'[Date] )
        )
    )
RETURN
  CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table','Table'[Symbol]=EARLIER('Table'[Symbol])&&
            'Table'[Date]<= EARLIER ( 'Table'[Date] )
                && 'Table'[Date] > _date
        )
    ) +0
Expected resilt(0) = 
VAR _date =
    CALCULATE (
        MAX(  'Table'[Date] ),
        FILTER (
            CALCULATETABLE ( 'Table', ALLEXCEPT(  'Table','Table'[Symbol]), 'Table'[Value2] = 1 ),
            'Table'[Date] <= EARLIER ( 'Table'[Date] )
        )
    )
RETURN
  CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table','Table'[Symbol]=EARLIER('Table'[Symbol])&&
            'Table'[Date]<= EARLIER ( 'Table'[Date] )
                && 'Table'[Date] > _date
        )
    ) +0

Result:

7.JPG

and if your want the date descending order, just add the way as below:

Add an index column for date by Symbol       

Date Index = RANKX(FILTER('Table 2','Table 2'[Symbol]=EARLIER('Table 2'[Symbol])),'Table 2'[Date],,DESC)

Then adjust formula as below:

new Expected resilt(1) = 
VAR _date =
    CALCULATE (
        MAX(  'Table 2'[Date Index] ),
        FILTER (
            CALCULATETABLE ( 'Table 2', ALLEXCEPT(  'Table 2','Table 2'[Symbol]), 'Table 2'[Value2] = 0 ),
            'Table 2'[Date Index] <= EARLIER ( 'Table 2'[Date Index] )
        )
    )
RETURN
  CALCULATE (
        COUNTROWS ( 'Table 2' ),
        FILTER (
            'Table 2','Table 2'[Symbol]=EARLIER('Table 2'[Symbol])&&
            'Table 2'[Date Index]<= EARLIER ( 'Table 2'[Date Index] )
                && 'Table 2'[Date Index] > _date
        )
    ) +0
new Expected resilt(0) = 
VAR _date =
    CALCULATE (
        MAX(  'Table 2'[Date Index] ),
        FILTER (
            CALCULATETABLE ( 'Table 2', ALLEXCEPT(  'Table 2','Table 2'[Symbol]), 'Table 2'[Value2] = 1),
            'Table 2'[Date Index] <= EARLIER ( 'Table 2'[Date Index] )
        )
    )
RETURN
  CALCULATE (
        COUNTROWS ( 'Table 2' ),
        FILTER (
            'Table 2','Table 2'[Symbol]=EARLIER('Table 2'[Symbol])&&
            'Table 2'[Date Index]<= EARLIER ( 'Table 2'[Date Index] )
                && 'Table 2'[Date Index] > _date
        )
    ) +0

 

Result:
9.JPG

 

and here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

The problem was with the formula for the binary column with the numbers 1 or 0. The formula consisted of an If statement ... I had unnecessary content in it. It worked with this change and with your help. 

Thank you very much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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