slicer to exclude from table

[darb1985]

Hello,

I hope someone can help with me this problem. 

I have a power bi report with three tables:

-Item (ItemID, Name)

-invoice (DocumentID, CustomerName)

-invoicelines (DocumentID, ItemID)

"item" table has a relationship to "invoiceLines" on 'ItemID' on both tables.

"invoice" table has a relationship to 'invoiceLines' on 'documentID' on both tables. Additionaly invoice table has a column to store customer name.

I have 2 visuals, one to display the customer name from the invoice table, the other one, to display itemID from the invoice table and the ItemName from the item table.

I have also one slicer, where you can search for items based on ItemName. I want to additionaly have a filter where I filter by article "Service flat rate*flat rate*" it shows me all customers where we have a sold a flat rate, and then I want to see with a negative filter (e.g. data backup) that it only shows the customers where we have a flat rate service, but no data backup.

This is to basically search for items not sold in combination with other items, like: we sold a notebook, but we could've bundled it with a monitor.

[johnbasha33 · Accepted solution]

@darb1985 

To achieve the desired filtering behavior in Power BI, you can use a combination of slicers and measures to filter customers based on the presence or absence of specific items, such as "Service flat rate" and "Data backup." Here's how you can set it up:

1. **Create Measures**:
Create measures to identify whether an item is present in an invoice. You can use DAX expressions like COUNTROWS and FILTER to count the occurrences of specific items in the invoicelines table.

```DAX
ServiceFlatRateSold =
CALCULATE(
COUNTROWS('invoicelines'),
FILTER(
'invoicelines',
RELATED('Item'[Name]) = "Service flat rate*flat rate*"
)
)
```

```DAX
DataBackupSold =
CALCULATE(
COUNTROWS('invoicelines'),
FILTER(
'invoicelines',
RELATED('Item'[Name]) = "Data backup"
)
)
```

2. **Create Customer Filter**:
Create a new measure to filter customers based on the presence or absence of specific items.

```DAX
FilterCustomers =
IF(
[ServiceFlatRateSold] > 0 && [DataBackupSold] = 0,
1,
0
)
```

This measure will return 1 if a customer has purchased the "Service flat rate" item and has not purchased the "Data backup" item.

3. **Apply Filter**:
Apply the "FilterCustomers" measure as a visual-level filter in your customer name visual. Set the filter to include only rows where "FilterCustomers" equals 1.

4. **Add Slicer**:
Add a slicer for the item names and use it to filter the data based on the items sold.

With this setup, when you select "Service flat rate*flat rate*" in the slicer, the customer name visual will only display customers who have purchased the "Service flat rate" item and have not purchased the "Data backup" item. Adjust the DAX expressions and filter logic as needed to fit your specific requirements and data model.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[darb1985]

Would you mind providing a pbix file for me to see? I guesss I don;t understand how the filter will be dynamic for the DAX in point 1, these values will be typed by the end user, so its not fixed 😞

I will mark your answer as solution, even though I am not sure how to implement it yet.

[johnbasha33]

@darb1985 Hi, thanks for accepting it. can you provide your pbix file with some sample data? i will implement the solution and will send it back to you. 

[darb1985]

I get the data from Business Central. but it  should look something like this:

Item:
id name
1 Datensicherung - Rücksicherung 
2 Datensicherung - Externe Aufbewahrung

Invoice:

id  customerName

1  Customer 1

2  Customer 2

InvoiceLines:

invioceId   itemId

1                 1

1                 2

2                  2

So if the user searches for 'Datensicherung', the results would be 'Customer 1' and 'Customer 2', now on an additional filter, the user searches for 'Rücksicherung', this one is expected to show the customer that do NOT have that, in this case 'Customer 2'.