Forum Discussion
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 !
- My fault you have to use curly braces {...} instead of (...)
Regards
15 Replies
- TomMartens
Super User
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
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 {}.
- TomMartens
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
- TomMartensMy fault you have to use curly braces {...} instead of (...)
Regards
