Count with condition

[Rth]

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!

[Anonymous]

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.

[Samarth_18 · Accepted solution]

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]

Try to create a measure with the code below:

 

Project20Plus =

    COUNTROWS(

        FILTER(

            ALL(CATS_Buchungen),

            CATS_Buchungen[HoursColumn]>20

    )

[Rth]

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..

[FreemanZ]

Aha, then try this

Project20Plus =

    CALCULATE(

        COUNTROWS(   DISTINCT(CATS_Buchungen[Project])

        ),

        FILTER(

            ALL(CATS_Buchungen),

            CATS_Buchungen[HoursColumn]>20

        )

)

[AilleryO]

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