Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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, if the % on each of the rows is above or equal to 0.8% then 1, else 0.
Also it should make the sum of the 1's for the process. Hence the activities: capacity assessment, demand alignment and standard routines are the only ones who meet the criteria and should have a 1 next to each row, and the capacity assessment process should have a 3 (the sum of the 3 activities with a 1)
How can this be done in a dax?
The % column of the image has the following dax:
Non Duplicate Hours Process/Activity 3 =
DIVIDE(
[Non Duplicate Hours Process/Activity Numerator],
[Non Duplicate Hours Process/Activity Denominator 3])
Thanks.
You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.
It shows empty:
Where:
Count IF 2 =
VAR _T1 =
FILTER (
ADDCOLUMNS (
VALUES ( Template[Facet] ),
"@%", [Non Duplicate Hours Process/Activity 3]
),
[@%] >= 0.8
)
VAR _Result = COUNTROWS ( _T1 )
RETURN
_Result
The table I am using has the following fields:
Any idea what could be wrong?
thanks.
Hi @lbendlin
With the second option you mention.
The dax I am trying to reference has these 2 pieces:
Non Duplicate Hours Process/Activity Numerator =
SUMX(
SUMMARIZE(
Template,
Template[Merged],
Template[Hours by Year]
),
Template[Hours by Year]
)
Could I have the if statetement incorporating these 2 measures within the dax?
thanks.
Hi @o59393 ,
not sure if I understood correctly, but if you want to count the facets which have & equal or greater to 0,8% you can try the following:
Count IF =
VAR _T1 =
FILTER (
ADDCOLUMNS (
VALUES ( Table[Facet] ),
"@%", [Non Duplicate Hours Process/Activity 3]
),
[@%] >= 0.8
)
VAR _Result = COUNTROWS ( _T1 )
RETURN
_T1
Sorry there is a mistake from my side.
Use _Result after RETURN instead of _T1
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.
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
Hi @ITManuel
Tried the measure but still not getting the sum. For example here should be 13 and I am getting 1:
Could it be missing sumx?
THanks.
Hi @ITManuel
Thanks, closer. But the total for Tier 1: Process is showing one as seen below:
Capacity planning from Tier 1: Process should have a result of 5 because 5 activities (Tier 2) contain a 1.
How can it make the sum of the 1s?
Thanks!
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |