Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
we have the following table:
Customer | ItemCategory |
A | Ticket |
A | Merchandise |
B | Merchandise |
C | Ticket |
This table has more then a thousand entries. I would like to filter all the customers who never bought an Item from the Ticket category. In this case, It should return B, as this customer only bought merchandise.
Any tips on how to achieve that?
Solved! Go to Solution.
Hi @Anonymous ,
Please create a measure like this.
Measure =
VAR x =
CALCULATE(
COUNT(Sheet2[ItemCategory]),
FILTER(
ALL(Sheet2),
Sheet2[Customer] = MAX(Sheet2[Customer]) && Sheet2[ItemCategory] = "Ticket"
)
)
RETURN
IF(
x = BLANK(),
MAX(Sheet2[Customer])
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please create a measure like this.
Measure =
VAR x =
CALCULATE(
COUNT(Sheet2[ItemCategory]),
FILTER(
ALL(Sheet2),
Sheet2[Customer] = MAX(Sheet2[Customer]) && Sheet2[ItemCategory] = "Ticket"
)
)
RETURN
IF(
x = BLANK(),
MAX(Sheet2[Customer])
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You can also try this measure
Customer who didn't by Ticket =
VAR allcustomers =
VALUES ( 'Table'[Customer] )
VAR BoughtTicket =
CALCULATETABLE (
VALUES ( 'Table'[Customer] ),
FILTER (
ALL ( 'Table' ),
'Table'[ItemCategory] = "Ticket"
)
)
RETURN
COUNTROWS (
EXCEPT (
allcustomers,
BoughtTicket
)
)
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi @Anonymous ,
You can try this measure.
A measure like this should work for you
Customers who did not buy tickets =
COUNTROWS (
FILTER (
VALUES ( Table[Customer] ),
ISBLANK ( CALCULATE ( COUNT ( Table[Category] ) ) )
)
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi
Can you try below.
CustomerCount(ExceptTicket) =
CALCULATE ( COUNTROWS ( Customer ), Customer[ItemCategory] <> "Ticket" )
Regards,
Venkata Nalla
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
106 | |
87 | |
75 | |
66 |
User | Count |
---|---|
125 | |
114 | |
98 | |
81 | |
73 |