How to write DAX code to Filter a list of more than 50 items on the list

[BILearnerToday]

Hi, Colleagues,

I would like to write a DAX which will replace Visual Filter which you have to tick on the Power BI on "Filter on this page" or "Filter on all pages"

Basically, I have a list of the countries table which are ticked on the Power BI box filter (Filter section on the Power BI )and this list is so big especially if I want to untick any counties, and I would like to have a DAX query that will filter this. 

Managing a list of the countries filter would be better on DAX rather than filter visual which is tedious work especially when you want to untick any bunch of countries but with the code is easy because you can see them more easily than filter visual.

Summary: List all the countries and how to filter them using DAX code instead of Filter visual where you tick/untick the item. I would like to use the DAX code to untick the List of countries (for example untick  20 countries from the list). Thanks in advance.

[v-yadongf-msft]

Hi @BILearnerToday 

This is my test table:

Please try following DAX:

Items more than 50 = 
CALCULATE(
    MAX('Table'[Items]),
     FILTER('Table','Table'[Items]>=50)
     )

Then create a table visual like below, you will get a list of more than 50 items.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AntonioM]

@v-yadongf-msft , @v-yanjiang-msft ,

I have to say I think you've provided and marked a solution for a different question than the one @BILearnerToday asked. If I'm not mistaken their ask is concerning filtering a list of coutries not where the value of another column 'Items' is greater than 50, but instead the number of countries on the list itself is large (they've specified "more than 50").

"Summary: List all the countries and how to filter them using DAX code instead of Filter visual where you tick/untick the item. I would like to use the DAX code to untick the List of countries (for example untick  20 countries from the list). Thanks in advance."

[AntonioM]

You could achieve this with a new column, something along the lines of 

Column = IF ( [Country] in {
"Spain", 
"France",
"Germany",
"Japan"
}, 1)

You will still need the filter, but now you only have only value to choose. If you now filter where [Column] = 1, this will be equivalent to filtering on each of the countries in your list. You can then manage the list instead of ticking/unticking each individually.