Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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.
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |