Count X Filter Help

[michael_knight]

Hi,

I'm having problems with one of my measures. 

Total Disqualifed - 3rd party - No Spam & Duplicates = COUNTX(FILTER('Lead',( 'Lead'[Disqualified Reason] = "Cannot Contact" ||
'Lead'[Disqualified Reason] = "Not Interested" || 
'Lead'[Disqualified Reason] = "Wrong Number"  &&
'Lead'[Enquiry Source] = "Facebook" &&
'Lead'[Enquiry Source] = "Email Campaign" && 
'Lead'[Enquiry Source] = "Twitter") && 
'Lead'[statecode] = 2), [statecode])

 I'm trying to count the amount of values there are from all the above

I have another measure that works and counts the amount of disqualified from Twitter, Email Campaign and Facebook

Total Disqualified - 3rd Party = COUNTX(FILTER('Lead',( 'Lead'[Enquiry Source] = "Twitter" ||
'Lead'[Enquiry Source] = "Email Campaign" || 
'Lead'[Enquiry Source] = "Facebook") && 
'Lead'[statecode] = 2), [statecode])

I think the problem is that I'm doing something wrong when using multiple different fields in the same measure

Can anoyone help me with the issue?

Thanks,

Mike

[v-juanli-msft]

Hi @michael_knight 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[RandyPgh]

The working calculation that you provide pulls results where the Enquiry Source is either Facebook, Email Campaign, or Twitter. The top not-working calculation requires that Enquiry Source be all three of these, because it uses && (AND). I am guessing that you probably want the logic below which instead uses an || (OR) connection for those fields. If not, please provide more explanation of the goal.

Note that using the IN operator simplifies the code.

Total Disqualifed - 3rd party - No Spam & Duplicates =

    COUNTX (

        FILTER (

            'Lead',

         'Lead'[Disqualified Reason] IN { "Cannot Contact", "Not Interested", "Wrong Number" }  &&

            'Lead'[Enquiry Source] IN { "Facebook", "Email Campaign", "Twitter" } &&

                'Lead'[statecode] = 2

       ),

      [statecode]

    )

[jdbuchanan71 · Accepted solution]

Hello @michael_knight 

I think it is the && conditions stacking on each other filtering out all reults.  GIve this a try:

Total Disqualifed - 3rd party - No Spam & Duplicates =
COUNTX (
    FILTER (
        'Lead',
			'Lead'[Disqualified Reason] IN ( { "Cannot Contact", "Not Interested", "Wrong Number" } )
			&& 'Lead'[Enquiry Source] IN ( { "Facebook", "Email Campaign", "Twitter" } )
			&& 'Lead'[statecode] = 2
    ),
    [statecode]
)