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=0,COLUMN(D5:Z5)))>14,1))>0,"Yes","No")

 

I use the above formula to see if an employee/contractor works for more than 14 conscutive days

 

I am looking for a similar DAX expression that will give me a boolean return if 14, greater than zero, consecutive values occour

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.

3 Replies

  • 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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Here is an example. The data for my purpose is from a SQL database and the end result will be in Power BI.

    The basic premise is that nobody should work greater than 14 days without one day off. We will use this to allow managers to monitor and correct the behaviour.