topic Re: Slicer to filter two column without context in DAX Commands and Tips

Slicer to filter two column without context

sonm10 — Fri, 18 Mar 2022 18:57:26 GMT

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.

Re: Slicer to filter two column without context

Fowmy — Fri, 18 Mar 2022 20:28:54 GMT

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 file

Exists = VAR __SellerBuyerSelected = VALUES( BuyerSeller[Buyer] ) RETURN CALCULATE( COUNTROWS(table1), Table1[Buyer] IN __SellerBuyerSelected || Table1[Seller] IN __SellerBuyerSelected )

Re: Slicer to filter two column without context

sonm10 — Mon, 21 Mar 2022 17:01:04 GMT

Fowmy, Thanks for your solution. It is working as expected however, when we only have the buyer, seller and cost columns in the table, the silcer is not working. For that, I have added an additional field (although not required in that particular table) for the slicer to work.