topic Re: Exclude certain string values in DAX Commands and Tips

Exclude certain string values

Anonymous — Tue, 01 Nov 2022 10:15:57 GMT

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

,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

Re: Exclude certain string values

amitchandak — Tue, 01 Nov 2022 13:57:14 GMT

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/

Re: Exclude certain string values

Anonymous — Tue, 01 Nov 2022 15:02:57 GMT

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