Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help creating a measure that counts rows

I have a column that shows whether an account is closed or not with a flag.  So the column is named ClosedFlag and the value is either True or False.  I need two measures.  One measure that counts the number of true flags and one that counts the number of false flags.

  • Anonymous's avatar
    Anonymous
    7 years ago

    This depends slightly on whether you have the column as a Boolean or not, but try the following:

     

    True Flag Count =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[ClosedFlag] = TRUE )
    
    False Flag Count =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[ClosedFlag] = FALSE )

    If the column is Text rather than Boolean, then try putting quotations "" around TRUE and FALSE.

     

    To explain quickly, the CALCULATE wraps around a table expression that counts the rows in a filtered table. The first parameter is the table expression and the second is the filter on that table expression.

  • Anonymous's avatar
    Anonymous
    7 years ago

    Give this a try:

    True Flag Count =
    VAR _trueCount =
        CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[ClosedFlag1] = TRUE )
    RETURN
        IF ( ISBLANK ( _trueCount ), 0, _trueCount )

    I've used a variable so the CALCULATE function only needs to evaluate once.

  • Anonymous's avatar
    Anonymous
    7 years ago

    I would say use COUNTROWS inside a CALCULATE, in the same way to the code I posted earlier. That way you avoid doing a direct COUNT of the Boolean.

    Also, instead of multiplying the measure by 100 to get a decimal, you can format the measure directly to be a Percentage type by going to the Modelling tab.

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    This depends slightly on whether you have the column as a Boolean or not, but try the following:

     

    True Flag Count =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[ClosedFlag] = TRUE )
    
    False Flag Count =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[ClosedFlag] = FALSE )

    If the column is Text rather than Boolean, then try putting quotations "" around TRUE and FALSE.

     

    To explain quickly, the CALCULATE wraps around a table expression that counts the rows in a filtered table. The first parameter is the table expression and the second is the filter on that table expression.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thats exactly what I used minus the CALCULATE portion at the begining.  Didn't work originally so I converted the column to text and added the ""

    • Anonymous's avatar
      Anonymous
      Not applicable

      How can I show a value of 0 instead of blank if the filtered results has no closed/open flags?

      • Anonymous's avatar
        Anonymous
        Not applicable

        Give this a try:

        True Flag Count =
        VAR _trueCount =
            CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[ClosedFlag1] = TRUE )
        RETURN
            IF ( ISBLANK ( _trueCount ), 0, _trueCount )

        I've used a variable so the CALCULATE function only needs to evaluate once.