COUNTIF with multiple criteria

[lewdow]

Hi - I have a single data table with the following fields for the :

ACCOUNT_NAME

PRODUCT_NAME

STATUS

DATE_GENERATED

And the measure I want to generate has the following logic: for the most recent month of data at any one point in time, COUNT the number of ACCOUNT_NAME where (PRODUCT NAME = "Bus" and STATUS = "First") AND (PRODUCT_NAME = "Car" and STATUS = "Second").

Any DAX support would be appreciated!

Thanks

Lewis

[v-xicai]

Hi  @lewdow  ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @lewdow ,

You may create measure like DAX below.

Count ACCOUNT_NAME = 
CALCULATE(COUNT(Table[ACCOUNT_NAME]),FILTER(ALLSELECTED(Table),YEAR([DATE_GENERATED])=YEAR(TODAY())&&MONTH([DATE_GENERATED])=MONTH(TODAY())&&(PRODUCT NAME = "Bus" && STATUS = "First") || (PRODUCT_NAME = "Car" && STATUS = "Second")))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quic

[camargos88]

@lewdow ,

I would try like:

Measure =
VAR _lastDate = CALCULATED(MAX(DATE_GENERATED); ALL(Table))
RETURN 
CALCULATE(COUNT(Table[ACCOUNT_NAME]),
FILTER(Table,
YEAR(DATE_GENERATED) = YEAR(_lastDate) &&
MONTH(DATE_GENERATED) = MONTH(_lastDate) &&
(
(PRODUCT_NAME = "Bus" && STATUS = "First") ||
(PRODUCT_NAME = "Car" && STATUS = "Second")
)
)

Did I answer your question? Mark my post as a solution!
Ricardo

[az38]

Hi @lewdow 

smth like

Measure = 
CALCULATE(
COUNT(Table[ACCOUNT_NAME]),
FILTER(
ALL(Table),
(PRODUCT NAME = "Bus" && STATUS = "First") || (PRODUCT_NAME = "Car" && STATUS = "Second")
)
)

[lewdow]

Thanks - this workds perfectly - how would you incorporate 'for the latest month of data' into that though?