Customer Purchased has purchased items A,B and C

[tstackhouse]

I would like to be able to show customers and their purchases when a customer purchases items A, B and C. I would need the report to capture only when all three items are purchased. Preferably though an exception report of some type.

An example of the information I have:

Customer            Date                Item

1                        6/1/2017              A

1                        6/5/2017              B

1                      6/10/2017              C

1                      6/12/2017              E

2                      6/11/2017              C

2                      6/14/2017              B

3                        6/4/2017              A

3                      6/12/2017              B

3                      6/20/2017              C

3                      6/13/2017              D

In the Example above I would like to show customer 1 and the items(A,B,C)

I do not want to see customer 2 because customer 2 only purchased items B and C

I do want to see customer 3,  customer 3 purchased items A, B and C

Any advice or insight on this issue would be much appreciated!

[Ashish_Mathur]

Hi,

Try this

=COUNTROWS(FILTER(Data,COUNTROWS(Data)=3))

[tstackhouse]

Attached is an example of what I would like to see, I have created this on Tableau.

I realize it may be a little confusing but on my attached picture below. "Patdob" would be "Customer" and "O/B/MR" would be the "Item". The second attached picture was the table calculation used to calculate if a customer contained all three items.

Any help on this would be much appreciated

[v-sihou-msft]

@tstackhouse

According to your description, you need to select Customers which has purchase selected items with AND logic. Right?

You can create a Flag measure to check if a custom purchase all selected items. Then put this measure into Visual Level Filter.

Flag = 
var contain=CALCULATE (
    DISTINCTCOUNT ( Table3[Item] ),
    FILTER (
        ALLSELECTED ( Table3 ),
        SEARCH ( CALCULATE ( MAX ( Table3[Item] ) ), CONCATENATEX ( ALLSELECTED ( Table3[Item] ), Table3[Item], "," ),, -1 ) > 0
    ),
    VALUES ( Table3[Customer] )
)
var selected=CALCULATE(DISTINCTCOUNT(Table3[Item]),ALLSELECTED(Table3))
return
IF(selected=contain,1,0) 

Regards,

[tstackhouse]

@v-sihou-msft

Thank you for the response to my problem.

I have a couple of questions on the formula:

1. I am revieving an error message that says: "A circular dependency was detected"  I have checked the formula for errors but I cannot find any. (attached below)

2. I am assuming where you put the quotation marks in the formula it is just a repeat of the "Table3[Item]", is this correct?

3. For the second formula,"selected", I am returning 5's for all of my values, if  "=CALCULATE(DISTINCTCOUNT(Table3[Item]),ALLSELECTED(Table3))" the formula? And is "IF(selected=contain,1,0)" just a reference to what the formual is calculating.

Again, thank you for all of your work into this matter!

Thanks

[tstackhouse · Accepted solution]

In the previous post I posted an incorrect formula, this is how it should read, both versions return a circular dependency error: