Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
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.
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.
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?
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
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?
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 ""
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |