Solved: Evaluating a customer list and returning true/fals...

Anonymous · ‎11-25-2019

In one table ("CustomerOrders") I have a list of customer orders as follows:

CustomerID	OrderNum	ProductCategory
A	1	Mountain bike
A	2	Road bike
B	3	Mountain bike
C	4	Road bike
C	5	Mountain bike
D	6	Mountain bike

In another table ("Customers") I have a list of customers and I want to have a column that returns True/False whether the customer has ever purchased a road bike. How would I code that true/false road bike column?

CustomerID	Road Bike Purchased?
A	?
B	?
C	?
D	?

parry2k · ‎11-25-2019

@Anonymous assuming you have relationship between these table on custom, add new measure as below

Road Bike Purchased? =
VAR __countRoadBike = CALCULATE ( COUNTROWS ( OrderTable ), OrderTable[Product Category] = "Road Bike" )
RETURN
IF (  __countRoadBike > 0, "Yes", "No" )

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

Anonymous · ‎11-25-2019

Hi ,

You can try this measure

roadbikecalculation = if(calculate(count(Sheet1[ProductCategory]),Sheet1[ProductCategory]="Road Bike"),"Yes","No")

Please let me know if this helped you