Set up a filter on a visual using IN operator in Power BI report builder - Multiple Values

[OceanExplorer]

Hello, in Report builder, I wanted to set up a filter on a Matrix visual with multiple values. For example, the orginal Matrix will return results below:

The desired result after applying visual level filter:

So far, I have tried:

This keeps adding """ around each value and gave an error. I also tried "Orange", "Green", "Blue" for Values, didn't work either. 

 

 

[Cookistador · Accepted solution]

hi @OceanExplorer 

Is it that you need to achieve, I apply Black, and my table returns

If I return a color, I will have the standard table 

To do that, you have to achieve a few steps

1. Insert a new row to your table/matrix (outside of the group) (select the title row and click on insert row)
   
2. Right click on the new row and select Row Propreties...
3. In visibility, select Show based on an expression
4. use the following expression
   =iif(CountRows("Tablix1") > 0, true, false)
   Just becareful with Tablix1, it is the name of my table, so replace it by the name of the table you created (you can find the name in Tablix propreties)
5. Enjoy your visual which works like required

If it is not working, do not hesitate to ask for more help 🙂

[OceanExplorer]

Hi @Cookistador I posted a new quesion a little similar to this, if you have time to help take a look, thanks! 
https://community.fabric.microsoft.com/t5/Desktop/In-Report-Builder-matrix-how-to-make-Row-Groups-st...

[v-sdhruv]

Hi @OceanExplorer ,
Additionally, you can try these approaches:

1. =CStr("Orange,Green,Blue").Split(",")
2."Orange" "Green" "Blue" - No comma, only space
3."Orange";"Green";"Blue" - Try giving semicolon
Refer - Using the IN operator to filter in reporting services

Hope this helps!

[Cookistador]

Normally this is the way

If I execute the report, it returns what I want:

But becareful, if you reopen the proprety window, it will remove your "", and you will have to readd them

[OceanExplorer]

Hi Cookistador, this method - "Orange", "Green", "Blue" for Values - actually works, I realized that I mistakenly used a wrong field name in the filter setting previously. 

A follow up question, I noticed that the entire Matrix visual will disappear if the filter values are not included, for example, if I set Values to "Purple", "Black". How could I make the Matrix table still appears, just showing an empty table like this:

Thank you!

 

[Cookistador · Accepted solution]

hi @OceanExplorer 

Is it that you need to achieve, I apply Black, and my table returns

If I return a color, I will have the standard table 

To do that, you have to achieve a few steps

1. Insert a new row to your table/matrix (outside of the group) (select the title row and click on insert row)
   
2. Right click on the new row and select Row Propreties...
3. In visibility, select Show based on an expression
4. use the following expression
   =iif(CountRows("Tablix1") > 0, true, false)
   Just becareful with Tablix1, it is the name of my table, so replace it by the name of the table you created (you can find the name in Tablix propreties)
5. Enjoy your visual which works like required

If it is not working, do not hesitate to ask for more help 🙂

[OceanExplorer · Accepted solution]

Hi @Cookistador , it's working for me! Thanks so much! 

I noticed that the Expression is - Set expression for: Hidden, so need to keep this in mind when writing the formula.