Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Exclude values from a table dynamically based on a attribute via a slicer

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 C...
  • PaulDBrown's avatar
    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 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