Problem with referencing a varchar field in measure

[WDHB01]

Hi Im trying to create a measure which displays total row count of a table where the [MessageStatusCode] field = 0.  The field datatype is varchar and length is 3

The values in the [MessageStatusCode] are either 0, 1 or 2

This is the code I used.

TotalTxts2 = CALCULATE( COUNTROWS('pas tbl_OPSMSSent'),FILTER('pas tbl_OPSMSSent',VALUES('pas tbl_OPSMSSent'[MessageStatusCode]="0"))

This did not give an error just no data.

Then this after reading a post.

TotalTxts2 = CALCULATE( COUNTROWS('pas tbl_OPSMSSent'),FILTER('pas tbl_OPSMSSent',FIRSTNONBLANK('pas tbl_OPSMSSent'[MessageStatusCode],1)="0"))

This did not give an error just no data.

Really new to dax so appreciate any help.

[amitchandak · Accepted solution]

@WDHB01 , This should simply work like

TotalTxts2 = CALCULATE( COUNTROWS('pas tbl_OPSMSSent'),FILTER('pas tbl_OPSMSSent','pas tbl_OPSMSSent'[MessageStatusCode]="0"))

if there are spaces

TotalTxts2 = CALCULATE( COUNTROWS('pas tbl_OPSMSSent'),FILTER('pas tbl_OPSMSSent',trim('pas tbl_OPSMSSent'[MessageStatusCode])="0"))

or

TotalTxts2 = CALCULATE( COUNTROWS('pas tbl_OPSMSSent'),FILTER('pas tbl_OPSMSSent',left('pas tbl_OPSMSSent'[MessageStatusCode],1)="0"))

[WDHB01]

Thanks second option worked.