Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
let's say I have this sales table:
CustomerID | ProductID | ProductName |
1 | 5 | Shirt |
1 | 6 | Pants |
2 | 5 | Shirt |
2 | 8 | Shoes |
In my table visual, I only want to select the Customer ID. Without any filters it looks like this:
CustomerId |
1 |
2 |
Now I want to select all customers who bought shirts(5), but exclude them, if they bought pants(6). So in the end only customerId 2 should be displayed, because he bought a shirt and no pants. How would I do that dynamically via a slicer?
Thanks!
Solved! Go to Solution.
@Anonymous
Here is one way.
Firstly, create two Dimension tables for Product name. Name one"Select Product" and link it to the Data table in a one-to-many relationship. Name the other "Exclude Product", and leave this table disconnected.
The model looks like this:
Next create a measure to show the IDs which include the selected products but exclude the products you wish:
IDs Selected =
VAR products = VALUES('DataTable'[CustomerID]) //Creates a table of values of IDs with selected products
VAR ExcProducts = CALCULATETABLE(VALUES('DataTable'[CustomerID]),
REMOVEFILTERS('Select Product'[ProductName]),
TREATAS(VALUES('Exclude Product'[ExclProduct]), 'DataTable'[ProductName]))
//Creates a table of values of IDs with products you wish to exclude
RETURN
IF(ISFILTERED('Exclude Product'[ExclProduct]),
COUNTROWS(
EXCEPT(products, ExcProducts)), //Creates a table of IDs which include products selected but not products excluded
COUNTROWS(VALUES('DataTable'[CustomerID]))) //Returns a count of IDs selected if no product is excluded
Create another measure to filter the "Excluded product" slicer to only show products which are not selected in the "Select Product" Slicer and add this to the Filters on this visual in the filter pane setting the value to 1:
Filter Exclude table =
VAR Prods = VALUES('DataTable'[ProductName])
VAR _Exclude = VALUES('Exclude Product'[ExclProduct])
RETURN
COUNTROWS(
EXCEPT(_Exclude, Prods))
And you will get this:
I've attached the sample PBIX file for you
Proud to be a Super User!
Paul on Linkedin.
@Anonymous
Here is one way.
Firstly, create two Dimension tables for Product name. Name one"Select Product" and link it to the Data table in a one-to-many relationship. Name the other "Exclude Product", and leave this table disconnected.
The model looks like this:
Next create a measure to show the IDs which include the selected products but exclude the products you wish:
IDs Selected =
VAR products = VALUES('DataTable'[CustomerID]) //Creates a table of values of IDs with selected products
VAR ExcProducts = CALCULATETABLE(VALUES('DataTable'[CustomerID]),
REMOVEFILTERS('Select Product'[ProductName]),
TREATAS(VALUES('Exclude Product'[ExclProduct]), 'DataTable'[ProductName]))
//Creates a table of values of IDs with products you wish to exclude
RETURN
IF(ISFILTERED('Exclude Product'[ExclProduct]),
COUNTROWS(
EXCEPT(products, ExcProducts)), //Creates a table of IDs which include products selected but not products excluded
COUNTROWS(VALUES('DataTable'[CustomerID]))) //Returns a count of IDs selected if no product is excluded
Create another measure to filter the "Excluded product" slicer to only show products which are not selected in the "Select Product" Slicer and add this to the Filters on this visual in the filter pane setting the value to 1:
Filter Exclude table =
VAR Prods = VALUES('DataTable'[ProductName])
VAR _Exclude = VALUES('Exclude Product'[ExclProduct])
RETURN
COUNTROWS(
EXCEPT(_Exclude, Prods))
And you will get this:
I've attached the sample PBIX file for you
Proud to be a Super User!
Paul on Linkedin.
@Anonymous
Are you going to enter the product IDs in the formula or have you got them in two separate lists in tables?
________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Add the following measure to your table visual:
Cust M =
IF( 5 IN VALUES(Table13[ProductID]) && NOT 6 IN VALUES(Table13[ProductID]) , 1 , 0)
________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi,
this would work for these two IDs, but not if I want to select/exclude multuple productIds. Is there anyway to make this dynamic?
User | Count |
---|---|
106 | |
90 | |
82 | |
76 | |
73 |
User | Count |
---|---|
112 | |
101 | |
96 | |
74 | |
67 |