Exclude certain string values

[Anonymous]

Hi, 
I am a Qlikview developer that has been working a lot with PBI lately.

I have a case were I want to calculate "Number of Answered Emails" and exclude all rows where column Workgroup includes GDPR string. 

As an example the workgroups can be Arket_Austrian_GDPR_Emails and we have a lot of these kind of values that contains GDPR. 

Is there any best practice for these kind of calculations? I have a lot of calculations that needs the same treatment in terms of excluding multiple string values.

I have tried creating a metric for this, see below:

Contact per Order % =

VAR IFGDPR = IF(

                SEARCH("*Ar*",MAX(dim_brand[Brand]),1,BLANK())>=1

                    ,1

                        ,0 )

VAR NoEmails = CALCULATE([No of Emails], dim_wrapup[Wrap Up Group] <> "BO Action", dim_wrapup[Wrap Up Group] <> "BO Support", dim_wrapup[Wrap Up Group] <> "H&M CLUB", IFGDPR = 0 )

RETURN

NoEmails

FYI, No of Emails are already aggregated in a SUM function in a measure before.

BR Fredrik

[amitchandak · Accepted solution]

@Anonymous , Try like

CALCULATE([No of Emails], Filter( dim_wrapup , not( dim_wrapup[Wrap Up Group] in {"BO Action", "BO Support", "H&M CLUB" })) , filter( dim_brand, SEARCH("*Ar*",dim_brand[Brand],1,0) = 0 ) )

Check this too

http://dataap.org/blog/2019/04/22/difference-between-calculate-with-and-without-filter-expression/

[Anonymous]

@amitchandak 
Hi,

Thanks for the reply! 
Your solution was exactly what I was looking for. This actually made me understand so much more about DAX and how to think... 

DAX vs Qlik is very different in terms of logic in some cases and this helped me. I had a lot of cases were I needed to do filtering of this kind in some key metrics.

Have nice day! 

BR Fredrik