Filter Dataset by Secondary Column Only

[OpenRoad]

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!

[v-jiascu-msft]

Hi, @OpenRoad

You could not achieve 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 =
IF (
    HASONEVALUE ( 'DataTable2'[Store] ),
    IF (
        MIN ( 'DataTable'[Product] )
            IN CALCULATETABLE (
                VALUES ( 'DataTable'[Product] ),
                FILTER (
                    ALL ( 'DataTable' ),
                    'DataTable'[Store] = VALUES ( DataTable2[Store] )
                )
            ),
        "y",
        BLANK ()
    ),
    0
)

Hope this would be a little help.

 

[v-jiascu-msft]

@OpenRoad

Hi,

Did it work? What else can I do for you?

Best Regards!

Dale

[OpenRoad]

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

[OpenRoad]

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.