Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Frequency over range

I have an Excel formula that looks for a frequency over a range and when it sees greater than 14 consecutive values over zeo it flags 'Yes'   =IF(SUM(IF(FREQUENCY(IF(D5:Z5>0,COLUMN(D5:Z5)),IF(D5:Z5...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    To my knowledge, FREQUENCY() is not available for DAX. So I need some data sample to clarify your scenario.

    Please provide me with more details about your table and the expected output or share me with your pbix file after removing sensitive data.

     

    Or since you could successfully add a flag column in Excel, you could directly import it to Power BI instead of creating it in PBI Desktop.

     

    Best Regards,
    Eyelyn Qin

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

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

You can check for over 14 by seeing if every day of the last 15 had a positive value.

 

It's difficult to write anything specific without more information on what tables/columns/measures you're working with, but maybe something like this:

IsOver14Days =
VAR CurrDay =
    CALCULATE ( SELECTEDVALUE ( DateTable[Date] ) )
RETURN
    SUMX (
        FILTER (
            ALL ( DateTable ),
            DateTable[Date] <= CurrDay
                && DateTable[Date] > CurrDay - 15
        ),
        IF ( [Value] > 0, 1 )
    ) = 15