Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Intersection Issue in DAX

Hi All,

 

Since i didnt get proper solution in last post. I m posting another time.

I have two columns with name Product Code and Customer Name. I want to calculate the unqiue number of customers who bought common Products. For easy going, i m giving sample data here:

ProductCodeCustomerCode
A1001C/001
A1001C/002
B1004C/001
B1004C/004
B1004C/003
C1005C/001

I want following Output:

I drag ProductCode in slicer and select only A1001, then distinct count of customer code should be calculated who bought this Product.

My Output should be 2

Now suppose i multiselect A1001 and B1004 and i should get 1 in output as there is only customer who bought both products.

I also want to see the list of customer who bought both products only or depending upon if i multiselect 3 or 4 Product code.

Basically list of only unique Customers who bought products Based on AND.

 

Any Help would be appreciated

 

Thanks in advance

Need Help

11 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    6 years ago

    Anonymous 

     

    Try this MEASURE

     

    Measure =
VAR myproducts =
    COUNTROWS ( ALLSELECTED ( Table1[ProductCode] ) )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( Table1[CustomerCode] ),
            COUNTROWS (
                INTERSECT (
                    ALLSELECTED ( Table1[ProductCode] ),
                    CALCULATETABLE ( VALUES ( Table1[ProductCode] ) )
                )
            ) = myproducts
        )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI Zubair_Muhammad 

       

      I' m getting values by selecting individual ProductCode but getting Blank values if i do Multiselect on ProductCode.

      Can you suggest for this one?

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        6 years ago

        Hi Anonymous 

         

        I am attaching the pbix file with your sample data

         

        I get correct figures as you mentioned.

        Please take a look and let me know

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    6 years ago

    Anonymous 

     

    Similarly if you want the list of such customers in a card visual, you can use this MEASURE

     

    ListOfCustomers =
VAR myproducts =
    COUNTROWS ( ALLSELECTED ( Table1[ProductCode] ) )
RETURN
    CONCATENATEX (
        FILTER (
            VALUES ( Table1[CustomerCode] ),
            COUNTROWS (
                INTERSECT (
                    ALLSELECTED ( Table1[ProductCode] ),
                    CALCULATETABLE ( VALUES ( Table1[ProductCode] ) )
                )
            ) = myproducts
        ),
        [CustomerCode],
        UNICHAR ( 10 )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

      Zubair_Muhammad 

      I want the list of customers in the Grid only as i have more than 10,000 customers.

      how do i go for it??

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        6 years ago

        Anonymous 

         

        We can do it with another MEASURE and use it as a VISUAL filter.

         

        But is my formula working now?