cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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

Regards


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

12 REPLIES 12
TomMartens
Super User II
Super User II

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Hi Tom!!

 

Thank you for all your contributions to this site. I was wondering if you can help me. I have tried 

 

TotalFires = COUNTX(Query1,Query1[INCIDENT_CATEGORY] IN {"Accidental Dwelling Fire",
"Accidental Non Domestic Fire",
"Accidental Other Primary Fire",
"Accidental Vehicle Fire",
"Accidental Small Fire",
"Chimney Fire",
"Deliberate Dwelling Fire",
"Deliberate Non Domestic Fire",
"Deliberate Small Fire",
"Deliberate Other Primary Fire",
"Deliberate Vehicle Fire"})
 
and the following error is returned when I try to add it to a visualisation:
 
The function COUNTX cannot work with values of type Boolean
 
I don't think my column INCIDENT_CATEGORY has any Boolean. Are you able to assist? 
 
I'm trying to get a filtered set / count of INCIDENT_CATEGORY where INCIDENT_CATEGORY contains the values in my expression
 
Thank you!
Dan
 

Hey @Anonymous ,

 

maybe this measure will provide what you are looking for:

TotalFires = 
SUMX(Query1,
IF(Query1[INCIDENT_CATEGORY] IN 
{"Accidental Dwelling Fire",
"Accidental Non Domestic Fire",
"Accidental Other Primary Fire",
"Accidental Vehicle Fire",
"Accidental Small Fire",
"Chimney Fire",
"Deliberate Dwelling Fire",
"Deliberate Non Domestic Fire",
"Deliberate Small Fire",
"Deliberate Other Primary Fire",
"Deliberate Vehicle Fire"}
, 1 , BLANK())
)

If this does not solve your problem, please start a new question.

The error is due to the fact that COUNTX is not able to count BOOLEANs, a boolean is returned by column IN {...}


Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

@TomMartens

 

 

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 {}

@mculloa {} are required to indicate that you are creating a list of items.

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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!

 

Anonymous
Not applicable

Hello @Anonymous,

 

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

 

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

Regards


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Solved my problem :). Thank you!

olesojg
Resolver II
Resolver II

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.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.