Forum Discussion

H_insight's avatar
H_insight
Icon for Helper V rankHelper V
3 years ago
Solved

Count with condition per customer per id

 

Hiya,

 

I am trying to write a measure to count the Products per customer and per id, where the "Product" column = "Product 1" and does not contain any "Product 2" for the same ID.

here s my attempt

 

 

Measure = 
CALCULATE(COUNTA(Table1[Product]),
        FILTER(Table1, Table1[Product] = "Product 1"), FILTER(Table1, 
 NOT(CONTAINSSTRING(Table1[Product],"Product 2"))))

 

 

I am getting the result count of 3, but I am looking only for 2 as a result (example below)

 

Sample Data:

IdCustomerProduct

1Customer 1Product 1
1Customer 1Product 2
1Customer 1Product 3
1Customer 1Product 4
2Customer 1Product 1
2Customer 1Product 4
2Customer 1Product 5
3Customer 1Product 1
3Customer 1Product 8
3Customer 1Product 3
3Customer 1Product 4

 

Thanks

DAX
Need Help DAX
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    thank you for your feedback.

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file whether it suits your requirement.

     

     

     

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file, whether it suits your requirement.

     

     

     

    Expected result measure: =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            DISTINCT ( 'Table'[Id] ),
            "@productonecondition", CALCULATE ( COUNTROWS ( FILTER ( 'Table', 'Table'[Product] = "Product 1" ) ) ),
            "@producttwocondition", CALCULATE ( COUNTROWS ( FILTER ( 'Table', 'Table'[Product] = "Product 2" ) ) )
        ),
        [@productonecondition] >= 1
            && [@producttwocondition] = 0
    )
)
  • H_insight's avatar
    H_insight
    Icon for Helper V rankHelper V
    3 years ago

    Hi Jihwan_Kim ,

     

    I am still getting the same result for 3 records, and my goal is to show only 2 records

    Goal:

     

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      3 years ago

      Hi,

      thank you for your feedback.

      I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file whether it suits your requirement.

       

       

       

       

      • H_insight's avatar
        H_insight
        Icon for Helper V rankHelper V
        3 years ago

        Jihwan_Kim Super Star🌟!! Thank you very much. I have to say, formatting DAX does help a lot in understanding the logic behind the code. 👍