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...
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
Responsive Resident
Responsive ResidentWhat 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