locked
DAX - Measure count active products RRS feed

  • Question

  • Tried a lot but didn't get any expected result.

     

    I need a DAX measure which shows me the second result:

     

    Based on my 2 tables, I need to know how many products is active if they appear in the second table - then 1, else 0

     

    Product1
    A
    B
    C
    D
    E
    F

     

    Product2
    A
    J
    K
    A
    B
    J
    F
    F
    F

     

    The result should be:

     

    Product Measure
    A 1
    B 1
    C 0
    D 0
    E 0
    F 1

     

    Count: 3

     

     

     

    Monday, August 27, 2018 12:40 PM

Answers

  • Hi EvertonGomes12,

    Thanks for your question.

    Please create measures called MIsActive and MActiveAccount as below:

    MIsActive =
    IF (
        COUNTROWS ( FILTER ( Product2, Product2[Product] = MAX ( Product1[Product] ) ) )
            >= 1,
        1,
        0
    )
    
    MActiveCount =
    SUMX ( VALUES ( Product1[Product] ), [MIsActive] )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Tuesday, August 28, 2018 12:38 AM