Forum Discussion
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.
- Anonymous7 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.
- Anonymous7 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.
- Anonymous7 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
- AnonymousNot 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.
- AnonymousNot 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 ""
- AnonymousNot applicable
How can I show a value of 0 instead of blank if the filtered results has no closed/open flags?
- AnonymousNot 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.