Filter Dataset by Secondary Column Only

Question

I'm trying to figure out how to filter a dataset to by a secondary column only.

In Excel the non-optimal workaround is to have two slicers (eg. one for store and one for product and after selecting the store, set the second slicer to select multiple and use a shift click to multi-select all the products then remove the store slicer. In PowerBI the shift click method does not work and the number of products is too large to quickly select by clicking on individually. How can I set it up to filter the dataset by a secondary column only?

For example:

Data Table

Store	Product	Sales
Steve's Auto	Tires	10
Steve's Auto	Oil	4
Rachael's Auto	Tires	9
Rachael's Auto	Brakes	12
Jeff's Auto	Brakes	4
Jeff's Auto	Oil	3


Slicer1
Steve's Auto	<-- Selected Steve's Auto
Rachael's Auto
Jeff's Auto


Resulting Table (Products Steve's Auto Sells)

Store	Product	Sales
Steve's Auto	Tires	10
Steve's Auto	Oil	4
Rachael's Auto	Tires	9
Jeff's Auto	Oil	3

Thanks!

openroad · Answer

So far I've been able to still return the total number of Product Sales for all Store's while only showing products for the selected store by using Measure = Calculate(sum(Table[Sales]),all(Table[Stores])) but have not been able to have the other relevant Stores appear in the table.

openroad · Answer

Also, attempts to use a ALL command on the 'store' column have been met with data type errors.

v-jiascu-msft · Answer

&nbsp;Hi,&nbsp;OpenRoad&nbsp;You could not achieve&nbsp;your goal if you do this in one table. Creating another table would make this easier.1. Create a new table with only stores. (Copy the table, keep just column STORE, delete duplicates. )2. Delete the relationship if Power BI creates it automatically.3. Create a measure like this.Measure&nbsp;=IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;HASONEVALUE&nbsp;(&nbsp;'DataTable2'[Store]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MIN&nbsp;(&nbsp;'DataTable'[Product]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IN&nbsp;CALCULATETABLE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(&nbsp;'DataTable'[Product]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL&nbsp;(&nbsp;'DataTable'&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'DataTable'[Store]&nbsp;=&nbsp;VALUES&nbsp;(&nbsp;DataTable2[Store]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"y",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BLANK&nbsp;()&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;0)&nbsp;Hope this would be a little help.&nbsp;&nbsp;&nbsp;

v-jiascu-msft · Answer

OpenRoad&nbsp;Hi,&nbsp;Did it work? What else can I do for you?&nbsp;Best Regards!Dale

Forum Discussion

Filter Dataset by Secondary Column Only

4 Replies

Related content

secondary axis

Secondary filter

Refresh dataset api

Filter CSV dataset based on a column

Combo Chart Disable Secondary Y Axis