cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kbwissi Frequent Visitor
Frequent Visitor

Multiple Slicers on One Table

I have a very large table that I would like to apply some slicers to. Because the table is so large, I'd like to have multiple slicers acting on it. Below is an example of my data:

 

Vehicle ID -  Miles  - Num of Vehicles

      1         - 10000 - 20

      1         - 12000 - 20

      1         - 10000 - 22

      2         - 10000 - 20

      2         - 12000 - 20

      2        - 10000 - 22

 

I would like to have slicers for each column/vehicle ID combo. For example, I need the user to select for Vehicle ID 1, how many miles and the number of vehicles. For vehicle ID 2, I need them to select the miles and number of vehicles, etc.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Seward12533 New Contributor
New Contributor

Re: Multiple Slicers on One Table

For this you will need multiple disconnected slicers with measures to harvest the user choices and set defaults if no choice is made. Then use Dax in measures to filter individual measures.

Here is an example using multiple date ranges to overlap two data sets that I helped someone build for a different solution. https://1drv.ms/u/s!AuCIkLeqFmlhhJkIcpsdkWzMMqeBBw

It’s more complex than what you need but demonstrates the principles
3 REPLIES 3
Seward12533 New Contributor
New Contributor

Re: Multiple Slicers on One Table

so build them Smiley Happy  I'd recommend setting up some Lookup Tables to linked to your data and filter on those. Will also function as bridge tables when you start using multiple source (FACT) tables.

 

Few ways to do do this.

 

in Power Query

  1. make a REFERENCE copy of your query that collects the data (Right Click and hit REFERENCE)
  2. Select only the column you want to slice on and then REMOVE OTHER COLUMNS (right click on column header or from Remove columns button on ribbon)
  3. Then REMOVE Duplicates
  • Repeat 1-3 for each of your slicers link to your FACT table in your model and insert slicers visuals for each of them.

In DAXX

  1. Create a new Table from the Modeling Ribbon
  2. Use this DAX PAttern
  3. Vehicle IDs = VALUES(table[Vehicle ID])
  4. Repeat 1-3 for each of your slicers link to your FACT table in your model and insert slicers visuals for each of them.
kbwissi Frequent Visitor
Frequent Visitor

Re: Multiple Slicers on One Table

I sort of follow... but how would it work if for Vehicle ID 1 I wanted to select 10,000 miles and for Vehicle ID 2 I wanted to select 12,000 miles?

Seward12533 New Contributor
New Contributor

Re: Multiple Slicers on One Table

For this you will need multiple disconnected slicers with measures to harvest the user choices and set defaults if no choice is made. Then use Dax in measures to filter individual measures.

Here is an example using multiple date ranges to overlap two data sets that I helped someone build for a different solution. https://1drv.ms/u/s!AuCIkLeqFmlhhJkIcpsdkWzMMqeBBw

It’s more complex than what you need but demonstrates the principles