Only show columns for which a value has been chosen in slicer (slicer has many values!)

[sha823]

I am new to Power BI. I have seen other posts asking the same question as my post, but I cannot use those solutions, because my slicers have too many values in them.

Let's say I have a table similar to this:

Corporation	Chain	Store	Product	Rating
Corp. A	Chain A	Store A	Apple	3.1
Corp. A	Chain A	Stora A	Orange	2.9
Corp. A	Chain A	Store B	Apple	4
Corp. A	Chain B	Store C	Orange	3.1
Corp. A	Chain B	Store C	Banana	2.7
Corp. A	Chain B	Store D	Banana	4
Corp. B	Chain C	Store E	Apple	3
Corp. B	Chain C	Store F	Orange	1.8
Corp. B	Chain C	Store F	Banana	4.8
Corp. B	Chain D	Store G	Banana	2.4
Corp. B	Chain D	Store H	Orange	3.5
Corp. B	Chain D	Store H	Banana	3

So you can see, it is several companies and daughter companies, that rate various products differently. Note that each column can have many duplicate values, even though each store only has one rating for each product.

I wish to make slicers based on the values in the columns. One slicer where you can choose corporation, one where you can choose the chains you're interested in, and one slicer where you can choose stores. Then the table should show the average rating of a product, based on your choice.

For instance, if I choose "Chain A" and "Chain D" in my slicer, then it should show the average rating that those chains gave to each product (apple, orange and banana).

However, it's important that it does not show any other columns. So if I choose "Chain A" and "Chain D" in the Chain-slicer, the only columns in my table should be "Chain", "Product" and "Average Rating".

I have seen other posts that asked for similar things, but I cannot use those solutions, simply because there are too many values to choose from in my slicers (because the columns have too many unique values). In those posts, they made DAX code where you wrote individually all the options in the slicers. But I might have e.g. 50 stores in my slicer (and they all have names that are long and difficult to type).

Is there a way to solve this problem without having to write each slicer option by hand in DAX code?

[amitchandak]

@sha823 , Data filter and column/value(measure) slicer are two different thing.

You can use the field parameter both Axis/Measure slicer. with Axis slicer you can choose measures or switch measure.

Ideally, if filtering a data row(like "Chain A"), it will remove that data from the context

Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE
Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f
Field Parameters- Conditional Formatting: https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf

[sha823]

Thank you for response. However, I don't think this is what I want to do.

In your first video and your articles, you are making a slicer where you can choose different columns. This is not what I want to do. I have one slicer for each column, and the items in the slicer are the column values. For instance, if the slicer is based on the column "Store", then the values in the slicer are the various store names.

With the SWITCH code in your TOPN article, I am left with the same problem as I mentioned in my post: I have too many slicer options to write them all in DAX.

For instance, in your code here you create two items in your slicer, "Brand" and "Category":

TOP2 Quick = SWITCH([Selected Parameter], "Brand" , 
CALCULATE([Net], TOPN(2,ALLSELECTED('Item'[Brand]), [Net],DESC), 
values('Item'[Brand]))
 ,CALCULATE([Net], TOPN(2,ALLSELECTED('Item'[Category]), [Net],DESC), values('Item'[Category]))
)

If I were to use code like this, there would be 100 lines of code that I have to write manually, with names that are much more complicated than "Brand" and "Category".

So in your example, my slicer wouldn't contain the items "Brand" and "Category", but rather the type of brands and categories. And there might be 50 brands in there.