Getting the Max Frequency of consecutive positive numbers in Power BI DAX

[geyrsh]

Hi, is there a way to get the max number of consecutive positive numbers in DAX?

For this table, the answer should be 3.

In Excel, it is written as an array formula: {=MAX(FREQUENCY(IF(E3:E37>0,ROW(E3:E37)),IF(E3:E37<=0,ROW(E3:E37))))}

I need to have this formula written as a DAX function. Thank you!

[saud968]

Create a calculated column to identify the groups of consecutive positive numbers.
Create a measure to find the maximum length of these groups.
Here’s the step-by-step DAX code:

Calculated Column:
GroupID =
VAR CurrentValue = 'YourTable'[YourColumn]
VAR PreviousValue =
CALCULATE(
MAX('YourTable'[YourColumn]),
FILTER(
'YourTable',
'YourTable'[Index] = EARLIER('YourTable'[Index]) - 1
)
)
RETURN
IF(CurrentValue > 0,
IF(PreviousValue > 0,
CALCULATE(
MAX('YourTable'[GroupID]),
FILTER(
'YourTable',
'YourTable'[Index] = EARLIER('YourTable'[Index]) - 1
)
),
MAX('YourTable'[GroupID]) + 1
),
BLANK()
)

Measure:
MaxConsecutivePositives =
VAR PositiveGroups =
ADDCOLUMNS(
FILTER(
'YourTable',
NOT(ISBLANK('YourTable'[GroupID]))
),
"GroupSize",
CALCULATE(
COUNTROWS('YourTable'),
ALLEXCEPT('YourTable', 'YourTable'[GroupID])
)
)
RETURN
MAXX(PositiveGroups, [GroupSize])


OR 

MaxConsecutivePositives =
VAR PositiveNumbers =
ADDCOLUMNS(
FILTER(
VALUES('YourTable'[YourColumn]),
[YourMeasure] > 0
),
"Group",
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
[YourMeasure] <= 0 &&
EARLIER([YourMeasure]) > 0
)
)
)
VAR MaxStreak =
MAXX(
SUMMARIZE(
PositiveNumbers,
[Group],
"Count",
COUNTROWS(PositiveNumbers)
),
[Count]
)
RETURN
MaxStreak

[Kedar_Pande]

Create a Calculated Column: 

IsPositive = IF([YourColumn] > 0, 1, 0)

Measure:
PositiveStreak = 
IF([IsPositive] = 1, 
1 + 
CALCULATE(MAX([PositiveStreak]), 
FILTER(YourTable, YourTable[IndexColumn] = EARLIER(YourTable[IndexColumn]) - 1)
),
0)

Measure:
MaxConsecutivePositives = 
MAXX(YourTable, [PositiveStreak])

The MaxConsecutivePositives measure will give you the maximum streak of consecutive positive numbers, which should be 3 in your case based on the example provided.

[geyrsh]

Hi @Kedar_Pande ,

The PositiveStreak measure, I am not sure how this works, since there is an iteration of PositiveStreak inside the measure. Maybe I need some polishing with my dax knowledge?? 😅

[lkalawski]

@Kedar_Pande 

Adding auto-generated answers and not checking if the result is positive is inappropriate and should not happen on this forum. By misleading in this way, the person asking does not get any valuable answer.

The code you provided returns the number of all values ​​that are positive, regardless of whether it is in a row or not.

Hi @geyrsh ,

To solve your problem I would suggest these steps:

1. Adding a column of type Date (a reference for sorting the values is required) can be accomplished by adding an additional column in Power Query. The Dates should be sorted, and an index should be added (which will be useful when, for example, the calendar is incomplete and a month is missing).

Date.FromText("01 " & [Month])

2. The index that was added will assist in the calculations. It is necessary to check the index of the row corresponding to the last negative value relative to the checked date. Once obtained, the difference between the indices should be calculated, allowing for the determination of the number of consistently positive values in a row for each date.

Days with positive values = 
VAR __SelectedIndex = MAX('Table'[Index])
VAR __AllIndexes = ALL('Table'[Index])
VAR __PositiveValues = CALCULATETABLE(VALUES('Table'[Index]), ALLSELECTED(), 'Table'[Value] >= 0)
VAR __NegativeValues = EXCEPT (__AllIndexes, __PositiveValues)

VAR __NearestNegative = 
    MAXX(FILTER(__NegativeValues, 'Table'[Index] <= __SelectedIndex), 'Table'[Index])

VAR __Result = 
    __SelectedIndex - __NearestNegative
RETURN 
    __Result

3. Now, it is necessary to extract the maximum value from this.

Max of consecutive positive values = MAXX(VALUES('Table'[Month Date]), [Days with positive values]) 

Memorable Member | Former Super User If I helped, please accept the solution and give kudos!  Linkedin

[geyrsh]

Hi @lkalawski ,

Tried the 'Days with positive values' measure but I am getting the "A function 'PLACEHOLDER" has been used in a True/False expression that is used as a table filter expression. This is not allowed." error. I created a date index for this, and the value column is a measure. Would you need more details? let me check if I can send a sample pbix file.

[lkalawski]

@geyrsh , please share the sample file. 

[geyrsh]

I am not a Super User, please see the link to the file below. Let me know of any issues

https://drive.google.com/drive/folders/1rr1yAwxZvD23qfv6GFoXSnMLzqgUascd?usp=sharing

[Kedar_Pande]

@lkalawski Have you tested the solution I have mentioned?

I have tested it and it works as expected

[lkalawski]

@Kedar_Pande 

The current edited solution works fine and I wish you to always provide well thought out and proven solutions.

Best regards

[geyrsh]

Hi,

How do I replace 'Your Table'[Your Column] with a measure in a measure group? The sample data I included is a date column and a measure. I got stuck in the EARLIER().

Thank you!