cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
regasanyoto Member
Member

How to use filter with multiple values in DAX?

Hello guys,

 

I am trying to create a measure TotalExaminationBacklog  which counts all the examinationsIDs with the status WAI, VER, APP, HEL and SCH. 

 

But it seems that my measure (see image below) doesnt give any result.  any suggestions?

 

Thanks !

 

 

 

2017-08-04 18_48_49-CapacityInsights - Power BI Desktop.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: How to use filter with multiple values in DAX?

My fault you have to use curly braces {...} instead of (...)

Regards
Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
olesojg
Advisor

Re: How to use filter with multiple values in DAX?

When filtering on the ID's try the following:

 

OR(

Explanations[StatusID] = "WAI",
Explanations[StatusID] = "VER",

Explanations[StatusID] = "APP", etc.

 )

I think the way you have it, you are concatenating all the values into 1 string, which doesn't exist.

Highlighted
Super User III
Super User III

Re: How to use filter with multiple values in DAX?

Hey,

 

my current favorite to check if one value is contained in a set of values is using the newer IN() operator

 

So you can write it like this

 

COUNTX(
  'Yourtable'
  ,'Yourtable'[YourColumn] IN ("WAI", "VER", "APP","HEL", "SCH")
) 

 

Edited 2017-11-09 (start):

The statement above will not work due a type, the correct statement uses curly braces, why is explained in my last post. The correct statement will look like

COUNTX(
  'Yourtable'
  ,'Yourtable'[YourColumn] IN {"WAI", "VER", "APP","HEL", "SCH"}
)

Edited 2017-11-09 (end):

 

 

Assuming that the STATUS comes from a slicer, this would also be possible

COUNTX(
  'Yourtable'
  ,'Yourtable'[Yourcolumn] IN (ALLSELECTED('MaybeAnotherTable'[ColumnUsedInSlicer]))
)

Maybe this idea helps you give another idea

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
regasanyoto Member
Member

Re: How to use filter with multiple values in DAX?

Hello @Tom,

 

thanks for your response.

unfortunatly the given ideas dont work 😞

 

please check the image below

 2017-08-07 11_06_32-CapacityInsights - Power BI Desktop.png

 

regasanyoto Member
Member

Re: How to use filter with multiple values in DAX?

i  just have the solution for this case..

 

Measure 3 = CALCULATE([TotalExaminations];
Examinations[exa_StatusID] = "WAI" ||
Examinations[exa_StatusID] = "VER" ||
Examinations[exa_StatusID] = "APP" ||
Examinations[exa_StatusID] = "HEL" ||
Examinations[exa_StatusID] = "SCH" )

 

Thanks anyway guys!

 

Super User III
Super User III

Re: How to use filter with multiple values in DAX?

My fault you have to use curly braces {...} instead of (...)

Regards
Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

mculloa Visitor
Visitor

Re: How to use filter with multiple values in DAX?

Thank you for this answer - specifically related to using "in ("value", "value", "value", . . .)".

 

I was struggling to find an alternative to using || and "or"

 

I did notice in my query I needed to modify the syntax by using a curly bracket because the system would not accept the parentheses: 

4_Stage_Count = CALCULATE(COUNT(Opportunities[AccountId]),Opportunities[Stage] in {"Closed Won", "Closed Lost"})

 

Let me know if anyone knows why the () had to be replaced by the {}

Super User III
Super User III

Re: How to use filter with multiple values in DAX?

Hey,

 

basically my first post contains a typo (I have been in a hurry).

 

IN

 

searches in column-table,

 

The most simple form to define a table with just one column is to use {"curly", "braces"}. This little example creates a table with on column and two rows. Pleas be aware that the table is defined w/o a table name and w/o a name for the column.

 

I will edit my post immediately.

 

Regards

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors