cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mayankkumar Regular Visitor
Regular Visitor

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mayankkumar Regular Visitor
Regular Visitor

Re: Intersection Problem

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] ) )
)
)))

View solution in original post

10 REPLIES 10
Super User
Super User

Re: Intersection Problem

Hi @mayankkumar 

 

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

 

mayankkumar Regular Visitor
Regular Visitor

Re: Intersection Problem

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!

Super User
Super User

Re: Intersection Problem

Hi @mayankkumar 

 

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

 

mayankkumar Regular Visitor
Regular Visitor

Re: Intersection Problem

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 

Super User
Super User

Re: Intersection Problem

Hi @mayankkumar 

 

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

 

mayankkumar Regular Visitor
Regular Visitor

Re: Intersection Problem

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:

Capture.PNG

 

Thanks!

 

Super User
Super User

Re: Intersection Problem

Hi @mayankkumar 

 

Please see the screenshot and explanation below.

image.png

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

 

mayankkumar Regular Visitor
Regular Visitor

Re: Intersection Problem

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!

mayankkumar Regular Visitor
Regular Visitor

Re: Intersection Problem

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] ) )
)
)))

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 153 members 1,691 guests
Please welcome our newest community members: