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 wo...
  • 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 )

     

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
     
  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    Aha, then try this

    Project20Plus =

        CALCULATE(

            COUNTROWS(   DISTINCT(CATS_Buchungen[Project])

            ),

            FILTER(

                ALL(CATS_Buchungen),

                CATS_Buchungen[HoursColumn]>20

            )

    )