Measure to Count rows with total 0

[Ryanb2412]

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.

Expired Contracts = CALCULATE(COUNTROWS(MRL), MRL[Days to Expiration] <> BLANK(), MRL[Days to Expiration] = 0)

Any help would be greatly appreciated!

[jdbuchanan71 · Accepted solution]

@Ryanb2412 

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.