Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Intersection Problem

CustomerNameProducts
MayankOCEG2
MayankBRT3
LithiaBRT3
LithiaJOG0
LithiaJHOT8
ChrilsHGY7
ShantanuJOG0
JenniferJOG0
JenniferOCEG2
JenniferJHOT8
JenniferBRT3
JenniferHGY7

 

HI, I'm not able to calculate the intersection in Power BI.

Problem- I have Customer name and Product name in one table as two different columns. If i click on customer name  , lets say, mayank and jennifer (multiselect), then number of products should be visible which is common in both. In my example its should be 2 (as product OCEG2 and BRT3 are the product purchased by Mayank and jennifer both.

 

Plus i want to show the common product name also which is common in both.

 

Please help!

Thanks in advance

Sean 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    for Intersection, i guess this below measure also helpful:

     


    IF (
    ISBLANK (
    CALCULATE(COUNTROWS (
    FILTER (
    SUMMARIZE (
    Cust_table,
    Cust_table[CUST_CODE],
    "ProductsBought", DISTINCTCOUNT ( Sales[SKU])
    ),
    [ProductsBought] = COUNTROWS ( VALUES ( Sales[SKU] ) )
     
    ))
    ))
    ,
    0,
    CALCULATE(COUNTROWS (
    FILTER (
    SUMMARIZE (
    Cust_table,
    Cust_table[CUST_CODE],
    "ProductsBought",DISTINCTCOUNT ( Sales[SKU])
    ),
    [ProductsBought] = COUNTROWS ( VALUES ( Sales[SKU] ) )
    )
    )))

10 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous 

     

    Please try the below DAX expression.

    Measure = 
VAR _names = ALLSELECTED( 'Table'[CustomerName] ) 
VAR _products = CALCULATE( COUNT( 'Table'[Products] ), _names ) 
RETURN IF( COUNTROWS( _names ) = _products, 1 )

    I've also attached a file with the example in the use.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

      I apologize for my mistake,

      But i want to multiselect Product instead of Customer and see how many customer bought both Product (If two products selected in slicer).

       

      Thanks!

      • Mariusz's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi Anonymous 

         

        Please try the below.

        Measure 2 = 
VAR _products = ALLSELECTED( 'Table'[Products] ) 
VAR _names =
ADDCOLUMNS( 
    VALUES( 'Table'[CustomerName] ),
    "xxxx", CALCULATE( COUNTROWS( 'Table' ), _products )
)
RETURN COUNTROWS( FILTER( _names, COUNTROWS( _products ) = [xxxx] ) )

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        Mariusz Repczynski