Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
Hi,
Did it work? What else can I do for you?
Best Regards!
Dale
Also, attempts to use a ALL command on the 'store' column have been met with data type errors.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |