Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
2 years ago

Count 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...
Need Help
Show and Tell
ITManuel's avatar
ITManuel
Icon for Responsive Resident rankResponsive Resident
2 years ago

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's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
2 years ago

Hi ITManuel 

 

I got this message:

 

Thanks.

 

  • ITManuel's avatar
    ITManuel
    Icon for Responsive Resident rankResponsive Resident
    2 years ago

    Sorry there is a mistake from my side. 

    Use _Result after RETURN instead of _T1

    • o59393's avatar
      o59393
      Icon for Post Prodigy rankPost Prodigy
      2 years ago

      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's avatar
        ITManuel
        Icon for Responsive Resident rankResponsive Resident
        2 years ago

        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