Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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
    6 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] ) )
    )
    )))
Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI,

Mariusz 

 

This DAX is working for me. But what if i want to filter out this DAX based on a Measure? (Lets say, i have a measure of average product sold and want to filter my DAX, given by you,based on that measure where average product sold is greater than 10)

Second thing is i'm still not able to get only customers name which bought both products. I'm getting all customer list: please refer below:

 

Thanks!

 

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Anonymous 

 

Please see the screenshot and explanation below.

Red frame matrix represents your previous requirement were you asked to count at the product level and total to including only Customers who bought all selected product, for that please use Measure 3.

 

Green frame table is Using Measure 2 where we are going back to the requirement where you wanted to see how many customers bought all selected products.

 

I'm sure that from the illustration you can see that this requirement are different.

 

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
Mariusz Repczynski

 

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
    6 years ago

    HI,

    Mariusz 

    My intersection problem is solved. Thank you so much.

    But measure 2 is not working for rest of the product combination. Can you please re-check Measure2?

     

    Thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 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] ) )
    )
    )))