Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

COUNTIF with multiple criteria

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

Need Help

5 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    smth like

    Measure = 
CALCULATE(
COUNT(Table[ACCOUNT_NAME]),
FILTER(
ALL(Table),
(PRODUCT NAME = "Bus" && STATUS = "First") || (PRODUCT_NAME = "Car" && STATUS = "Second")
)
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous ,

     

    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

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    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

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi  Anonymous  ,

     

    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