Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Intersection Problem

CustomerName Products Mayank OCEG2 Mayank BRT3 Lithia BRT3 Lithia JOG0 Lithia JHOT8 Chrils HGY7 Shantanu JOG0 Jennifer JOG0 Jennifer OCEG2 Jennifer JHOT8 ...
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] ) )
    )
    )))
Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity 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
    Icon for Community Champion rankCommunity 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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

       

      This measure is working but it is not giving me what i m looking for.

      If i am selecting product 'BRT3' and 'OCEG2' and put Measure2 (what you have created) in values and put Products in column as a matrix Report then it is showing me value of 2 in BRT3 and 2 in OCEG2 and 2 in Total also. The value 2 coming in Total is absolutely correct but BRT3 is bought by 3 customer, similarly OCEG2 is bought by 3 customers and i want to see values as 3 in both product column and 2 in total one.

      Currently i m seeing this one:

      BRT3OCEG2Total
      222

       

      What i want is here:

      BRT3OCEG2Total
      332

       

      Thanks!

      Mariusz 

      • Mariusz's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Hi Anonymous 

         

        Please see the below.

        Measure 3 = 
MINX( 
    'Table', 
    CALCULATE( 
        COUNTROWS( 'Table' ), 
        ALLSELECTED( 'Table'[CustomerName] ) 
    ) 
)

         

        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,

      Mariusz 

      You Measure is not working in my environment:

      ListOfCustomers =
      VAR _products = ALLSELECTED( SALESDATA_CASE1[ITEM_CODE])
      VAR _names =
      ADDCOLUMNS(
      VALUES( SALESDATA_CASE1[CustCode_CaseOne] ),
      "xxxx", CALCULATE( COUNTROWS( SALESDATA_CASE1 ), _products )
      )
      RETURN COUNTROWS( FILTER( _names, COUNTROWS( _products ) = [xxxx] ) )
       
      I m getting blank values, though actual value isnt blank. I m using Page level filters
      Any more information you required to help me on this?
      Thanks in Advance!