Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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!
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
92 | |
83 | |
62 | |
61 | |
58 |
User | Count |
---|---|
157 | |
118 | |
103 | |
76 | |
66 |