Dax formula returning different values from SQL query

[Anonymous]

I am trying to create a measure that counts the number of records that meet certain criteria, my SQL query returns the correct value but when I tried to convert that into DAX logic it seems to be counting all records and ignoring the filters I've included.

SQL Query:

Select	Count(AUM_SITE_ID)
From Collector.Collector.GEOHAZARDS_AUM_SITE_INVENTORY Inventory
Where Status = 'A' and INSPECTION_DUE = 0

Result from this query is 3.

DAX code:

AUM Inspection Due = CALCULATE(
                    COUNT('AUM'[AUM_SITE_ID]),
                    'AUM'[INSPECTION_DUE] = 0,
                    'AUM'[STATUS] = "A"
)

Result from this code is 1968. 

Thanks for the help!

[PhilipTreacy]

Hi @Anonymous 

Your DAX works fine with my sample data.

Can you provide your data, can't test properly without it.

Regards

Phil

[amitchandak · Accepted solution]

@Anonymous , need some sample data to test.

Try this measure

AUM Inspection Due = CALCULATE(
COUNT('AUM'[AUM_SITE_ID]),
filter('AUM', not(isblank('AUM'[INSPECTION_DUE])) && 'AUM'[INSPECTION_DUE] = 0 &&
'AUM'[STATUS] = "A"
))

[Anonymous]

This worked perfectly, thank you!