Multiple column filtering

[setis]

Dear experts, 

I'm trying to create a measure that filter 2 columns of the same table using the "SEARCH"

Measure 12 = 
CALCULATE(CALCULATE(DISTINCTCOUNT(Cases[CaseID]);
FILTER( ALL(Cases);
(SEARCH("Accident"; Cases[Event];;BLANK())) ||
(SEARCH("Extraordinary"; Cases[Cover];;BLANK())) ||
(SEARCH("Information"; Cases[Cover];;BLANK()))))

As you can see, I'm trying to get the cases where the column Event containts "Accident" or the column Cover contains "Extraordinary" or "Information". 

Is it possible to do this in a measure?

[amitchandak]

Try like

Measure 12 =
CALCULATE(CALCULATE(DISTINCTCOUNT(Cases[CaseID]);
FILTER( ALL(Cases);
(SEARCH("Accident"; Cases[Event];1;0)>0) ||
(SEARCH("Extraordinary"; Cases[Cover];1;0)>0) ||
(SEARCH("Information"; Cases[Cover];1;0)>0))))

[az38]

Hi @setis 

try more simple

Measure 12 = 
CALCULATE(CALCULATE(DISTINCTCOUNT(Cases[CaseID]);
FILTER( ALL(Cases);
SEARCH("Accident"; Cases[Event]) > 0 ||
SEARCH("Extraordinary"; Cases[Cover]) > 0 ||
SEARCH("Information"; Cases[Cover]) > 0 
))

[setis]

Thanks @az38 

I'm getting this error

[az38]

Ok

@setis 

try

Measure 12 = 
CALCULATE(CALCULATE(DISTINCTCOUNT(Cases[CaseID]);
FILTER( ALL(Cases);
SEARCH("Accident"; Cases[Event];;0) > 0 ||
SEARCH("Extraordinary"; Cases[Cover];;0) > 0 ||
SEARCH("Information"; Cases[Cover];;0) > 0 
))

[setis]

@az38 @amitchandak 

Thanks for your help. It's not working for me. 

I'm getting an unrealistic number and when a place the measure on a table with the CaseID, I'm getting the same number in all rows. 

[az38]

@setis 

show, please, data example, relationships and expected result

[setis]

@az38 

Please find a dummy file here https://drive.google.com/file/d/1-e0pVtF0N5uJ2zOO4_cfYcAM-ctFQsNe/view?usp=sharing 

The expected result is this:

Thanks in advance

[az38]

@setis 

wow. it look a little bit other task

you need this trigger row-by-row?

maybe you need a simple column ?

Column = 
IF(
(SEARCH("Accident"; Cases[Event];;0) + SEARCH("Extraordinary"; Cases[Cover];;0) +
SEARCH("Information"; Cases[Cover];;0) > 0;
1;
BLANK()
)

[setis]

Thanks @az38 ,

Yes, I need it on a row by row.

I'm getting a syntax error with the column you sent. Can you see what's wrong?

[az38 · Accepted solution]

@setis 

try to take all Cases into ordinary quota:

Column = 
IF(
(SEARCH("Accident"; 'Cases'[Event];;0) + SEARCH("Extraordinary"; 'Cases'[Cover];;0) +
SEARCH("Information"; 'Cases'[Cover];;0) > 0;
1;
BLANK()
)

or remove Cases at all

Column = 
IF(
(SEARCH("Accident"; [Event];;0) + SEARCH("Extraordinary"; [Cover];;0) + SEARCH("Information"; [Cover];;0) > 0;
1;
BLANK()
)

[setis]

Thank you! It works now.