topic Re: Count with condition in DAX Commands and Tips

Count with condition

Rth — Wed, 23 Nov 2022 11:17:07 GMT

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!

Re: Count with condition

FreemanZ — Wed, 23 Nov 2022 12:01:32 GMT

Try to create a measure with the code below:

Project20Plus =

COUNTROWS(

FILTER(

ALL(CATS_Buchungen),

CATS_Buchungen[HoursColumn]>20

)

Re: Count with condition

Rth — Wed, 23 Nov 2022 12:40:01 GMT

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

Re: Count with condition

Samarth_18 — Wed, 23 Nov 2022 12:58:07 GMT

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 )

Re: Count with condition

AilleryO — Wed, 23 Nov 2022 15:19:13 GMT

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

Re: Count with condition

FreemanZ — Wed, 23 Nov 2022 15:54:06 GMT

Aha, then try this

Project20Plus =

CALCULATE(

COUNTROWS( DISTINCT(CATS_Buchungen[Project])

FILTER(

ALL(CATS_Buchungen),

CATS_Buchungen[HoursColumn]>20

)

Re: Count with condition

Anonymous — Wed, 30 Nov 2022 07:55:58 GMT

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.