cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## How to filter a measure by multiple values

Hello, I would like to create a measure that returns the count of the rows of a table. I am able to use CALCULATE with a single value in the filter expression to get the measure to return the correct count of rows that contain that single value in that column. But I want to filter for not just one value, but for a handfull of values. Anyone know how to do this?

Here's a screenshot:

This successfully returns the number of rows with "Open" in the Stage column, but I want to return the number of rows that contain "Open", "Design" and others.

2 ACCEPTED SOLUTIONS

You can use double pipe || as a logical or

=CALCULATE([measure], table[column 1] = 1 || table[column 1] = 2)

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Or is a function that takes 2 parameters. Double pipe || is an inline syntax and you can have as many as you need.

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
9 REPLIES 9
Helper I

@Kashuo could you please share the solution , i have similar problem .

Helper I

Matt gave it, above. I used "Doublepipe" ||

You can use double pipe || as a logical or

=CALCULATE([measure], table[column 1] = 1 || table[column 1] = 2)

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

what does [measure] mean in your example?

New Member

When I use that formula, I get the error "The expression contains multiple columns, but only a single column ca be used in a True/False expression that is used as a table filter expression".

My measure is: COUNTA('table[Column])

Helper I

That works, thank you!!

Or is a function that takes 2 parameters. Double pipe || is an inline syntax and you can have as many as you need.

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Frequent Visitor

Hello Matt,

Is there a way to use the || to sort byt two columns?

I have a total ranking of mutiple columns columns. However, I also have a column with the number of surveys the customer took. I need to rank all customers with more than 10 surveys in the upper half of my table but then I also need to rank those with less than 10 surveys in the lower half of the table. Any suggestion on how to do that?

Thank you,

Lukas

I'm not clear on what you mean by "rank".  If you need to filter a table based on the values in 2 different columns

eg table[Col1] >=10 or table[Col2] <=10 then I would normally do this as follows

UNION(

filter(table,table[col1]>=10),

filter(table,table[col2]<=10)
)

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.