Forum Discussion
Exclude values from a table dynamically based on a attribute via a slicer
- 5 years ago
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 excludedCreate 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
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 š
- Anonymous5 years agoNot applicable
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?