Forum Discussion
o59393
Post Prodigy
Post ProdigyCount if with a measure
Hi Is it possible to create a count if with a measure? In the image below I have a capacity planning process with different activities (drilled down). The rule is simple, i...
ITManuel
Responsive Resident
Responsive ResidentSorry there is a mistake from my side.
Use _Result after RETURN instead of _T1
o59393Post Prodigy
Post Prodigy
I am getting closer to the solution.
I used:
Count IF =
var _x =
SUMX(
SUMMARIZE(
Template,
Template[Merged],
Template[Hours by Year]
),
Template[Hours by Year])
var _y =
CALCULATE(
[Non Duplicate Hours Process/Activity Numerator],
ALLEXCEPT(
Template,
Template[Function],
Template[Area],
Template[Name]))
var _z =
DIVIDE(_x,_y)
RETURN
IF(_z>=0.01,1,0)
It works well, the only thing still not doing is the sum of those who are above 1.
In the image below:
Next to capacity planning there should be a 5 which is the sum of the activities.
Any idea?
Thanks.
- ITManuel
What you are doing is not counting anything, but calculating your [ Count IF] measure for each line and its specific filter context in the visual.
Try this:
COUNT IF = VAR _T1 = FILTER ( ADDCOLUMNS ( VALUES ( Template[Facet] ), "@%", VAR _X = SUMX ( SUMMARIZE ( Template, Template[Merged], Template[Hours by Year] ), Template[Hours by Year] ) VAR _Y = CALCULATE ( [Non Duplicate Hours Process/Activity Numerator], ALLEXCEPT ( Template, Template[Function], Template[Area], Template[Name] ) ) VAR _Z = DIVIDE ( _X, _Y ) RETURN _Z ), [@%] >= 0.01 ) VAR _Result = COUNTROWS ( _T1 ) RETURN _Result