Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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:
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:
and here is sample pbix file, please try it.
Regards,
Lin
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:
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:
and here is sample pbix file, please try it.
Regards,
Lin
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |