Count if with a measure

[o59393]

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. 

[lbendlin]

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

[o59393]

@lbendlin 

Think I am getting closer:

Any idea here why the red shows up ?

[lbendlin]

I'd go with @ITManuel 's approach.  Is that not sufficient?

[o59393]

Hi @lbendlin 

Any idea here how to fix?

thanks.

[o59393]

Hi @lbendlin  @ITManuel 

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.

[o59393]

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.

[ITManuel]

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

[o59393]

Hi @ITManuel 

I got this message:

Thanks.

[ITManuel]

Sorry there is a mistake from my side. 

Use _Result after RETURN instead of _T1

[o59393]

Hi @lbendlin  @ITManuel 

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

[o59393]

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.

[o59393]

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!