Forum Discussion
Helper IMeasure to Count rows with total 0
Hey All,
I am trying to get a count of contracts that are expired in this dashboard. The data table looks like this:
Table Name "MRL"
| Employee | Days to Expiration |
| Emp A | 0 |
| Emp B | 45 |
Emp C | |
| Emp D | 0 |
| Emp E | |
| Emp F | 22 |
I am trying to build a measure that will count the # of expired contracts, but it isn't working for me. The card should show "2" as the answer if the measure functions correctly. The blank rows have to stay in the data because there are some reps where we do not have their contract date terms on file currently.
I initially tried using this formula below, but it was counting the blank rows as zeros, which I do not want it to do and was giving me an answer of "4".
Expired Contracts = CALCULATE(COUNTROWS(MRL), MRL[Days to Expiration] = 0)
So I changed to the formula below, but now the card visual just shows "(Blank)" even though the measure is formatted to provide a whole number.
Any help would be greatly appreciated!
Change your first formula to use the strict equal to of ==
Expired Contracts = CALCULATE(COUNTROWS(MRL), MRL[Days to Expiration] == 0)https://docs.microsoft.com/en-us/dax/dax-operator-reference
All comparison operators except == treat BLANK as equal to number 0, empty string "", DATE(1899, 12, 30), or FALSE. As a result, [Column] = 0 will be true when the value of [Column] is either 0 or BLANK. In contrast, [Column] == 0 is true only when the value of [Column] is 0.
1 Reply
- jdbuchanan71
Super User
Change your first formula to use the strict equal to of ==
Expired Contracts = CALCULATE(COUNTROWS(MRL), MRL[Days to Expiration] == 0)https://docs.microsoft.com/en-us/dax/dax-operator-reference
All comparison operators except == treat BLANK as equal to number 0, empty string "", DATE(1899, 12, 30), or FALSE. As a result, [Column] = 0 will be true when the value of [Column] is either 0 or BLANK. In contrast, [Column] == 0 is true only when the value of [Column] is 0.