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
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
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.