Count if value appears at least once a month and get toal of number of months it has appeard

[artjomsf]

I have a dataset with all hours logged by each person for each project, i.e. something like this:

I need to get a small summary table to count all employees who have logged Project A at least once per month and in the end count how many of those months were in total (in the period I select with sliced).

My current formula looks like this:

Measure = CALCULATE( 
DISTINCTCOUNT(Data[Name]),
FILTER(Data, CALCULATE( SUM(Data[Hours]), FILTER(Data, Data[Project] = "Prj A"))
> 0)
)

Then I aggregate in the matrix with Names as rows and months as columns. It partially works, giving me correct count (1 or blank):

But it: a) does not give me total count (so I cannot sort by total) and b) seems to be extremely slow.

How would it be possible to improve this formula?

Thanks!

[v-tangjie-msft · Accepted solution]

Hi @artjomsf ,

You can try using the SUMX function instead of SUM to improve the performance of your formula.

Measure = CALCULATE(
DISTINCTCOUNT(Data[Name]),
FILTER(Data,
SUMX(
FILTER(Data, Data[Project] = "Prj A"),
Data[Hours]
) > 0
)
)

To get the total count, you can create another measure 2 instead of measure.

Measure2 =
SUMX(
SUMMARIZE(
'Data',
'Data'[ColumnName],
"total",[Measure]
),
[total]
)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.