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 You all,
I have a customer table with certain Items in it but no amounts. I am trying to generate a list of all customers who have NOT bought a certain Item.
My data looks like this:
Customer | Item |
A | Bike |
A | Car |
B | Bike |
B | Bike |
C | Car |
C | Train |
D | Airplaine |
based on this data I would like a list of all customers who have not bought a Car. I am thinking in the of creating an If statement with true or False but I cannot quite work it out.
Customers with no Car |
B |
D |
Hope you can help me.
Solved! Go to Solution.
Hi @BobKoenen ,
Please check:
1. Create a Item Slicer table.
Item Slicer = VALUES('customer table'[Item])
2. Create measures.
Measure =
VAR SelectedItem =
SELECTEDVALUE ( 'Item Slicer'[Item] )
VAR Count_ =
CALCULATE (
COUNTROWS ( 'customer table' ),
'customer table'[Item] = SelectedItem
)
RETURN
IF ( Count_ >= 1, 1, 0 )
Title = "Customers with no " & SELECTEDVALUE('Item Slicer'[Item])
3. Create a table visual.
Then, you will get this:
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @BobKoenen ,
Please check:
1. Create a Item Slicer table.
Item Slicer = VALUES('customer table'[Item])
2. Create measures.
Measure =
VAR SelectedItem =
SELECTEDVALUE ( 'Item Slicer'[Item] )
VAR Count_ =
CALCULATE (
COUNTROWS ( 'customer table' ),
'customer table'[Item] = SelectedItem
)
RETURN
IF ( Count_ >= 1, 1, 0 )
Title = "Customers with no " & SELECTEDVALUE('Item Slicer'[Item])
3. Create a table visual.
Then, you will get this:
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is awsome!.
Thankx alot!
@BobKoenen ,Try
new Table = except(all(Table[customer]),filter(Table[Customer],Table[Item] ="Car"))
new Measure =
var _m1 =except(all(Table[customer]),filter(Table[Customer],Table[Item] ="Car"))
return
calculate([Measure], filter(Table,Table[customer] in _m1))
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 |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |