Forum Discussion

michael_knight's avatar
michael_knight
Post Prodigy
6 years ago
Solved

Count X Filter Help

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

Need Help
Tutorial Requests
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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]
)

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    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]
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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]

        )

     

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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