Warm tip: This article is reproduced from serverfault.com, please click

Wildcard search in the IN operator

发布于 2020-11-30 18:47:01

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.

Questioner
damo
Viewed
0
Alexis Olson 2020-12-01 03:03:40

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