Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 ...
Need Help
  • TomMartens's avatar
    TomMartens
    9 years ago
    My fault you have to use curly braces {...} instead of (...)

    Regards
TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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
 
  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    6 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      TomMartens

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Tom,

       

      I have come across a similar problem and your above solution works perfect for me. If wanted to use the above formular to filter by column 1 (Text values) and an additonal columns (Text values) how would that work?

       

      For example

      I want to sum column 1 

      Filter by column 2 (text values)

      and filter by column 3 (text values)

       

      Hope this makes sense.