Using Countrow if NOT a value

[Anonymous]

Hello all,

Im using the following formulae to count the occurances of the country codes in a field...

USA = CALCULATE ( COUNTROWS (Sheet1),((Sheet1[Citizen]="USA")))

GBP = CALCULATE ( COUNTROWS (Sheet1),((Sheet1[Citizen]="GBP")))

CAN = CALCULATE ( COUNTROWS (Sheet1),((Sheet1[Citizen]="CAN")))

what formulae would I use to count the rows of all of the other values (Not USA, GBP, CAN)

Thanks!

[Sean]

@Anonymous Or since you already have the 3 Measures you can just...

Others = COUNTROWS(Sheet1) - [USA] - [GBP] - [CAN]

EDIT: You probably even have some kind of a TOTAL Measure for ALL countries which you can use instead of COUNTROWS(Sheet1)

[chrisu · Accepted solution]

See https://msdn.microsoft.com/en-us/library/ee634237.aspx for all the operators you can use.  In this case you could do something like:

OtherValues = CALCULATE((COUNTROWS(Sheet1),(Sheet1[Citizen]<>"USA" && Sheet1[Citizen]<>"GBP" && Sheet1[Citizen]<>"CAN"))

[Anonymous]

Other = CALCULATE( COUNTROWS (Sheet1),

FILTER( Sheet1,

Sheet1[Citizen] <> "USA" && Sheet1[Citizen] <> "GBP" && Sheet1[Citizen] <> "CAN"))

[Habib]

You can use below formula:

COUNTROWS(FILTER(Sheet1,Sheet1[Citizen]<>"USA"&&Sheet1[Citizen]<>"GBP"&&Sheet1[Citizen]<>"CAN"))