Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

get counts based on nested conditions

Hi 

I have following table

 

ProductCustomer number
A1
B1
C1
F2
A2

 

Definition of customer: If the customer has purchased product A or B, only then we consider the customer. 

I want to create a count of customers based on these criteria. Also, it needs to add slicer products. However, whenever I select product F, technically it should show 1 customer. But it is showing blank.

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Anonymous 

    Thank you very much for your reply. Using the following DAX expression will get a count of different IDs:

    Count Customer =
VAR _seleted_product =
    SELECTCOLUMNS ( 'Table', 'Table'[Product] )
VAR _table =
    FILTER ( 'Table', 'Table'[Product] IN _seleted_product )
VAR _ID_AB =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[Product] IN { "A", "B" } ),
        'Table'[Customer number]
    )
RETURN
    IF (
        ISFILTERED ( 'Table'[Product] ),
        COUNTAX (
            SUMMARIZE (
                FILTER ( _table, 'Table'[Customer number] IN _ID_AB ),
                'Table'[Customer number]
            ),
            'Table'[Customer number]
        )
    )

     Here are the results:

    I've uploaded the PBIX file below for your reference.

     

     

    Best Regards

    Jianpeng Li

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

     

     

     

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    Anonymous 

    maybe you can try to create a new column

     

    count =
    VAR _A=maxx(FILTER('Table','Table'[Customer number]=EARLIER('Table'[Customer number])&&'Table'[Product]="A"),'Table'[Product])
    VAR _B=maxx(FILTER('Table','Table'[Customer number]=EARLIER('Table'[Customer number])&&'Table'[Product]="B"),'Table'[Product])
    RETURN IF(_A<>""&&_B<>"",1,0)
     
     
    then you just need to sum the count column
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you ryan_mayu 

    Hi, Anonymous 

    Based on the data you provided and the expected results described, I created a measure using the following DAX expression:

    Count Customer =
VAR _seleted_product =
    SELECTCOLUMNS ( 'Table', 'Table'[Product] )
VAR _table =
    FILTER ( 'Table', 'Table'[Product] IN _seleted_product )
VAR _ID_AB =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[Product] IN { "A", "B" } ),
        'Table'[Customer number]
    )
RETURN
    IF (
        ISFILTERED ( 'Table'[Product] ),
        COUNTROWS ( FILTER ( _table, 'Table'[Customer number] IN _ID_AB ) )
    )

    Here are the results:

    I've provided the PBIX file used this time below.

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      This is good. However, if I select A, B and F, it should return 2 as there are only two unique customer

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi, Anonymous 

        Thank you very much for your reply. Using the following DAX expression will get a count of different IDs:

        Count Customer =
VAR _seleted_product =
    SELECTCOLUMNS ( 'Table', 'Table'[Product] )
VAR _table =
    FILTER ( 'Table', 'Table'[Product] IN _seleted_product )
VAR _ID_AB =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[Product] IN { "A", "B" } ),
        'Table'[Customer number]
    )
RETURN
    IF (
        ISFILTERED ( 'Table'[Product] ),
        COUNTAX (
            SUMMARIZE (
                FILTER ( _table, 'Table'[Customer number] IN _ID_AB ),
                'Table'[Customer number]
            ),
            'Table'[Customer number]
        )
    )

         Here are the results:

        I've uploaded the PBIX file below for your reference.

         

         

        Best Regards

        Jianpeng Li

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