Forum Discussion
Helper ISlicer to filter two column without context
Hello,
I have a sample dataset as follows:
| Product | Category | Type | Seller | Buyer | Cost |
| Product1 | Cat1 | Type1 | A | C | 10 |
| Product2 | Cat2 | Type1 | C | A | 20 |
| Product3 | Cat2 | Type2 | C | B | 30 |
| Product4 | Cat1 | Type3 | A | C | 15 |
| Product5 | Cat3 | Type3 | B | A | 16 |
I have a column Seller and Buyer, that contains their name. A seller for one product can be a buyer for another product and vice versa. So basically, the two coumns will have many similar values. I need to have a single slicer to filter both these columns. The slicer have to be the union of these two column and should filter the rows if either or both the column has the slicer value.
I have implemented a solution as per this: Solved: Re: Once Slicer for multiple columns - Microsoft Power BI Community, where we create a measure on the union column. It was working as expected when the said two columns are available in the visual. The issue arises when the context changes, such as if we only select the Product and Amount fields in a table, the slicer will not have any effect. I require the slicer to filter the rows regardless of the context.
sonm10
I created a new table combining Seller and Buyer with distinct values then created a measure that identifies if the selected values (One or more) is found then filters the table. Check the attached fileExists = VAR __SellerBuyerSelected = VALUES( BuyerSeller[Buyer] ) RETURN CALCULATE( COUNTROWS(table1), Table1[Buyer] IN __SellerBuyerSelected || Table1[Seller] IN __SellerBuyerSelected )
2 Replies
- Fowmy
Super User
sonm10
I created a new table combining Seller and Buyer with distinct values then created a measure that identifies if the selected values (One or more) is found then filters the table. Check the attached fileExists = VAR __SellerBuyerSelected = VALUES( BuyerSeller[Buyer] ) RETURN CALCULATE( COUNTROWS(table1), Table1[Buyer] IN __SellerBuyerSelected || Table1[Seller] IN __SellerBuyerSelected )