Forum Discussion

rdops's avatar
rdops
Helper III
6 years ago
Solved

Count if hours = less than

Hi all, mee again needing some help.

I am doing a team capacity report and would like to show which roles have more available hours in a period. Say, 

If role name = Project Manager and the hours booked on a person who is a project manager is less than 70 (per month) than count.

 

I also wanted to show people on a specific role that have zero hours allocated on a month and again display them by skills. E.g

Mary and John are both Project managers and they both have zero hours booked in June. Paul, Ani and Trish are Copywriters and they all have zero hours booked. I want a report that tells me tha I have 2 Project managers and 3 Copywriters completely free. Then I want to be able to click on these roles and see who are the people within those roles who are completely free (or half allocated).

 

Hope it makes sense and hope you can hellp me 🙂 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi rdops 

    If all columns are in one table,

    It is better to create a date table and relate the date with your main table.

    Measure = CALCULATE(SUM('Table'[hours]),FILTER(ALLSELECTED('Date'),'Date'[year-month]=MAX('Date'[year-month])),VALUES('Table'[id]))

Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[id]),FILTER('Table',[Measure]<=70))

     

    Best Regards

    Maggie

     

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi rdops 

    For example, you have tables below:

    please create measures

    Measure = CALCULATE(SUM('Table'[hours]),FILTER(ALLSELECTED('Date'),'Date'[year-month]=MAX('Date'[year-month])),VALUES('Table 2'[id]))

Measure 2 = CALCULATE(DISTINCTCOUNT('Table 2'[id]),FILTER('Table',[Measure]<=70))

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

    • rdops's avatar
      rdops
      Helper III
      6 years ago

      Thanks for that, but how do you calculate if all the fields are on the same table? 

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi rdops 

        If all columns are in one table,

        It is better to create a date table and relate the date with your main table.

        Measure = CALCULATE(SUM('Table'[hours]),FILTER(ALLSELECTED('Date'),'Date'[year-month]=MAX('Date'[year-month])),VALUES('Table'[id]))

Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[id]),FILTER('Table',[Measure]<=70))

         

        Best Regards

        Maggie