Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Two slicers for one column

Hi guys,

 

I am quite new to PowerBI. I would like to have two slicers with products of different categories, which filter one column of a connected table with all product information of both categories.

 

Is there a possibility to do that with connected tables? I have already found some approaches with disconnected tables and measures, but this inhibits the connection to other visuals in the dashboard.

 

My inital ideas for a solution:

1) Slicers with an OR condition

2) Only slice products of a specific category in the info table --> Is there a function to limit a slicer only on flagged entries?

 

Thank you in advance!

10 REPLIES 10
AliceW
Impactful Individual
Impactful Individual

Hi Pechher,

May I ask why would you need TWO slicers based on the sames column?

If you want the user to be able to select only one Category, you can make the visual into a one-selection.

As for limiting the slicer to only some options, you can do that by creating a page or report level filter with that column, hide it, and within just select the options you want to be seen. The slicer will only display those.

Hope this helps,

Alice

Anonymous
Not applicable

Hi AliceW,

the user just has a tremendous advantage to have two slicers, in order to compare product counterparts of different categories in one visual. Referring to the screenshot, the selection could be Slicer 1: Product A & Slicer 2: Product F:

Bildschirmfoto 2021-04-10 um 13.14.12.png

Applying a page level filter on dublicated slicers with "Product" from "Aggregated Table" does not allow a selection in both slicers, because of the connections.

 

An OR condition could be a solution for the two slicers, right?

 

Hope this explaines my problem better.

AliceW
Impactful Individual
Impactful Individual

In the example you've shown, you can easily select two products with the help of one multi-selection visual...

Anonymous
Not applicable

Hi AliceW,

thanks for your support.

 

This is an easy possibility and it works.

However, it would be the objective to have two seperated slicers. One seperate slicer only for products of category A and one for products of category B, both slicing a singe column. Is this possible?

AliceW
Impactful Individual
Impactful Individual

Info: is having two and only two products in the visual the expected result? And is the list of products reasonably short? If yes to both, you could build two one column tables with the product names, then build two measures using SWITCH in the main table, then use those measures in the visual.

Tnt
Frequent Visitor

Hi.. 

Could you please elaborate on "then build two measures using SWITCH in the main table, then use those measures in the visual"

 

I am also facing the same need for comparing our and competitor products.

 

Thanks

AliceW
Impactful Individual
Impactful Individual

This is what I do to allow the users to switch the report visuals between currencies.

1. I created a table (Home / Enter Data) with two values: EUR and USD. You can use as many as you want. Called it 'Currency'. No need to create any relationship with this table.

2. In the table where you have your amounts, create this measure:

Total =

SWITCH(
TRUE(),
SELECTEDVALUE('Currency'[Currency])="EUR",sum(Opp[Total EUR]),
SELECTEDVALUE('Currency'[Currency])="USD", sum(Opp[Total USD]),
sum(Opp[Total EUR])
)
The last sum is just in case you use the measure without the switch...
3. In the report, you add a Slicer with the 'Currency' column. In its Selection Controls, make sure you have 'Single Select' on.
4. Build the visual with the new Total Measure.
 
You can apply this logic to your competitor issue; have two one-column tables and two switch formulas and two measures you get in the visual.
Tnt
Frequent Visitor

Hi Alice,

Thank you for the response. 

 

Plese find the problem details for which I am unable to realize the solution.

 

I have two or our product - AA and BB, which need to be compared individually with Competitor products XX, YY, and ZZ (see table below of possible comparisons)

 

My ProductBenchmark Product
AAXX
AAYY
BBYY
BBZZ

 

I created two Dimension table, one for my product and one for competitor products. The selection on our product (slicer) will refine the options in  competitor products (second slicer).

 

For eg: if i select AA in prodcut the options in second slicer will be XX and YY but not ZZ (as its not benchmarked against).

 

Now I need to select either XX or YY of our competitor product.

 

The selection then needs to identify related data from a fact table as shown below.

ProductTest result
XX8
YY9
ZZ7
AA7
BB8

 

For selection AA in first slicer and XX in second slicer, visualizer (Table/column) should give values for the selection.  (like the table below)

ProductTest result
AA7
XX8

 

Could you help me with this.

 

Thanks for your support.

 

Regards

Tnt

AliceW
Impactful Individual
Impactful Individual

Tnt, how does your data mode look like? Do you have two tables, one for your company's products and another for your competitors'? How do you connect the products?

Tnt
Frequent Visitor

Hi Alice, 

I have not found a good solution yet. 

 

I have 2 Dimension tables,

1. company's product (along with competitor prodcut; 2 column as shown in first table earlier)

2. competitor product (only one column - competitor product).

 

To have two interactive slicers, first slicer (1) is on my product (Table 1) and it is related to table 2 by bidierctional many to one relation. The second slicer (2) is on Table 2, which is filtered by slicer. 

The two tables (1 and 2) are expected to be fact table.

 

Thanks and regads.

Tinto

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.