Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tstackhouse
Helper III
Helper III

Customer Purchased has purchased items A,B and C

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!

1 ACCEPTED SOLUTION

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

Edit.PNG

 

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

 

Try this

 

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
tstackhouse
Helper III
Helper III

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

 

O B MR Example.PNGLoD Calc Tableau.PNG

 

 

 

 

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

23.PNG

 

Regards,

@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

 

Flag.PNG

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

Edit.PNG

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.