Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KGuyton
Helper II
Helper II

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.

3 ACCEPTED SOLUTIONS
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.

View solution in original post

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.

View solution in original post

Anonymous
Not applicable

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.

View solution in original post

9 REPLIES 9
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.

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

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.

This breaks my percentage calculation though.  I had to change the data back to boolean from text as well. error: 

The Function COUNT cannot work with values of type Boolean

 

Anonymous
Not applicable

You can use the quotations around the "TRUE" and "FALSE" if you want to keep the data type as Text.

Can you show me your percentage calculation?

I reverted back to text.  For future reference how could I calculate my measure using boolean?

 

Closed Percentage = v_rpt_Configs[Count of closed]/v_rpt_Configs[Count of Renewals]*100
Anonymous
Not applicable

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.

Thank you.

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 ""

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.