I am trying to create a measure :
Currently I have:
a_measure =
CALCULATE(SUM(table[Count]),
table[status] IN {"Duplicate","Returned - 10+ Days", "Returned - Inappropriate"}
)
table is my table
table[Count] is a helper column which has the value "1" for every row. (it's carried over while I work in both Excel and BI - i find it makes summing/counting easier)
What my measure says is: Sum column Count when the status is Duplicate OR Returned 10+ or Returned - Inappropriate.
What I'd like to do, rather than write out each different type of returned is: Sum Count When table[status] = Duplicate OR search("return",table[status],0,0)=TRUE
This would be really helpful. It might be nice to do it as
IN {"duplicate",search("Return%"))
But I think from the way IN works (https://www.sqlbi.com/articles/from-sql-to-dax-string-comparison/) you can't do that.
If I were doing this in R I could use something like filter(str_detect(status,"foo"))|status="duplicate"
If I used countrows, I could filter for each condition but I don't know how to apply the wildcard search in the filter. Can you apply a search in a filter? It feels like you can.
Alternatively, I could write a column in the table so that it assigns it to a group like Duplicate Returned (which has multiple types of Returned) Closed (which has multiple types of Closed)
and then use table[newfudgecolumn] IN {"Duplicate","Returned"}
Which would technically solve the problem but not in the way that I wanted.
Edit: I need to read this DAX Query - Filtering out values from a table in powerbi
Search rows with the same ID and apply AND filter to count a particular occurrence
Edit : I've skimmed those and while they work by counting rows that are filtered for each individual condition (which I understand) neither of them applies a search of the field in the individual condition (which is what I want).
tl;dr I want to apply a wild card search of a field to the IN operator
OR
I want to apply a wild card search of a field in the COUNTROWS operator.
Hope that makes sense.
You can do it like your R code:
a_measure =
CALCULATE (
SUM ( table[Count] ),
FILTER (
ALL ( table[status] ),
CONTAINSSTRING ( table[status], "Return" ) || table[status] = "Duplicate"
)
)
You can use table[status] IN { "Duplicate" }
if you prefer but I can't think of a way to do the wildcard within the IN
set.
Note that the CONTAINSSTRING function does allow for wildcards ?
and *
.
Thank you Alexis. This is exactly the kind of answer I was after for the filter. In R I might have done something like : list<-c("duplicate","removed","like","this") and then FILTER %IN%list to filter by a variable. Can you do that in DAX?
I don’t quite follow. Feel free to write a new post for additional questions not answered in this one.
I think a new question is best! Thanks again