Forum Discussion

Rth's avatar
Rth
New Member
3 years ago
Solved

Count with condition

Hi everyone, would anyone be able to help with a DAX calculation for the following?

 

I have a table including Name of the Project (projects are recurring) and Amount of Hours as columns.

Now I would like to create a Measure that determines the number of projects that have more than 20 hours. I already have a Measure that determines the number of projects:

Anzahl Projekte 2022 = DISTINCTCOUNT(CATS_Buchungen[Auftragsname]).

How do I add the condition that only projects over 20 hours are Counted? I have tried so Mandy things, but nothing worked.

 

Thanks in advance!

  • Samarth_18's avatar
    Samarth_18
    3 years ago

    Hi Rth ,

     

    You could try below code:-

     

    m =
VAR _calc =
    FILTER (
        SUMMARIZE (
            'Table (5)',
            'Table (5)'[Name of Project],
            "_SUM", SUM ( 'Table (5)'[Column1] )
        ),
        [_SUM] >= 20
    )
RETURN
    COUNTROWS ( _calc )

     

6 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    3 years ago

    Hi Rth ,

     

    You could try below code:-

     

    m =
VAR _calc =
    FILTER (
        SUMMARIZE (
            'Table (5)',
            'Table (5)'[Name of Project],
            "_SUM", SUM ( 'Table (5)'[Column1] )
        ),
        [_SUM] >= 20
    )
RETURN
    COUNTROWS ( _calc )

     

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago
    Try to create a measure with the code below:
     
    Project20Plus =
        COUNTROWS(
            FILTER(
                ALL(CATS_Buchungen),
                CATS_Buchungen[HoursColumn]>20
        )
    • Rth's avatar
      Rth
      New Member
      3 years ago

      Hi,

       

      thanks for your response.

      I think I did not express myself well enough. I attachted an example:

      The table's name is "Table".


      Regarding your code:

      I used DISTINCTCOUNT instead of COUNTROWS because I would like to count the distinct number of projects. But the code still did not work..

      • AilleryO's avatar
        AilleryO
        Memorable Member
        3 years ago

        Hi,

         

        The solution of Samarth_18 works fine and you don't need to do a distinct count, since because of summarize, your project we'll be only one time with the total time of each project.

        If i take the same formula as he proposed :

        Count of Project with more than 20h = 
        VAR TableTemp = FILTER( 
            SUMMARIZECOLUMNS( 'Table Name'[ID Project] , "Tot Hours Project" , SUM( 'Table Name'[Time spent column] ) ) ,
            [Tot Hours Project] > 20 ) //This is the filter on the column created above
        RETURN
        COUNTROWS( TableTemp ) //Counting the remaining rows after filtering gives us the result
         
        In this formula :
        1/ SUMMARIZE creates a table with a unique list of project and their respective total time
        2/ FILTER applies on the invisible column just created in SUMMARIZE, in my example named  "Tot Hours Project". This name is used 2 times, to create the column and its values, and then to filter.
        3/ Finally counting the remaing rows in that table gives us expected result.
         
        Hope it makes things more clear...
        Let us know
         
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Rth 

    Are you getting your problems solved ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

     

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