Forum Discussion
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...
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 )
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
Memorable Member
Memorable MemberHi,
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