Filter out a certain value but use that value in a slicer

[michael_knight]

Hi All,

Struggling on one of my dashboard. I'm trying to display the Qualification Rate of all our leads, excluding Facebook but also create a button which allows us to look at Facebook data when need be 

This is what is looks like without any slicers/filters being activated. Every enquiry source is included in these values 

This is what it looks like when I click on the Facebook slicer. This part works fine, it's just the previous image that I'm having problems with. I don't want Facebook included in the main data but I still access to look at it.

Any help would be welcomed. 

Thanks,

Mike

[michael_knight]

So essentially I need two measures, one that counts every Enquiry Source apart from Facebook and another that counts just Facebook. Never tried anything like this so would that be possible?

[v-lid-msft · Accepted solution]

Hi @michael_knight ,

We can try to use the following measure to meet your requirement.

Divide =
IF (
    SELECTEDVALUE ( 'Lead'[Category] ) = "Facebook",
    DIVIDE (
        COUNTX (
            FILTER (
                'Lead',
                ( 'Lead'[new_disqualifiedreason] = 279640000
                    || 'Lead'[new_disqualifiedreason] = 279640004
                    || 'Lead'[new_disqualifiedreason] = 279640002 )
                    && 'Lead'[Statecode] = 2
                    && [Category] = "FaceBook"
            ),
            [Statecode]
        ),
        COUNTROWS ( FILTER ( 'Lead', [Category] = "FaceBook" ) )
    ),
    DIVIDE (
        COUNTX (
            FILTER (
                'Lead',
                ( 'Lead'[new_disqualifiedreason] = 279640000
                    || 'Lead'[new_disqualifiedreason] = 279640004
                    || 'Lead'[new_disqualifiedreason] = 279640002 )
                    && 'Lead'[Statecode] = 2
                    && [Category] <> "FaceBook"
            ),
            [Statecode]
        ),
        COUNTROWS ( FILTER ( 'Lead', [Category] <> "FaceBook" ) )
    )
)

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

[michael_knight]

Good news, that worked!

Bad news, I want to do the same but as a count. Do you know how I would go about doing that?

I'll add the PBIX so you can see what I'm working with

Much appreciated @v-lid-msft 

https://www.dropbox.com/s/y2uggwbh58rvqg2/Disqualified%20Leads%20-%20Test.pbix?dl=0

[v-lid-msft]

Hi @michael_knight ,

If you means the "Total Leads" Measure in the buttom, we can use the following measure using the same logic.

Total Leads = 
IF (
    SELECTEDVALUE ( 'Lead'[Enquiry Source] ) = "Verso Group",
    COUNTROWS ( FILTER ( 'Lead', 'Lead'[Enquiry Source] = "Verso Group" ) ),
    COUNTROWS ( FILTER ( 'Lead', 'Lead'[Enquiry Source] <> "Verso Group" ) )
)

Or the measure "Lead - Count VG" that does not used in visual?

Lead - Count VG = 
IF (
    SELECTEDVALUE ( 'Lead'[Enquiry Source] ) = "Verso Group",
    DIVIDE (
        COUNTX (
            FILTER (
                'Lead',      
                     [Enquiry Source] = "Verso Group"
            ),
            [Statecode]
        ),
        COUNTROWS ( FILTER ( 'Lead', [Enquiry Source] = "Verso Group" ) )
    ),
    DIVIDE (
        COUNTX (
            FILTER (
                'Lead',
                     [Enquiry Source] <> "Verso Group"
            ),
            [Statecode]
        ),
        COUNTROWS ( FILTER ( 'Lead', [Enquiry Source] <> "Verso Group" ) )
    )
)

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[michael_knight]

Total Leads = 
IF (
    SELECTEDVALUE ( 'Lead'[Enquiry Source] ) = "Verso Group",
    COUNTROWS ( FILTER ( 'Lead', 'Lead'[Enquiry Source] = "Verso Group" ) ),
    COUNTROWS ( FILTER ( 'Lead', 'Lead'[Enquiry Source] <> "Verso Group" ) )
)

Nice one, this measure worked.

Total Disqualifed No Spam & Dupe - VG = 
IF (
    SELECTEDVALUE ( 'Lead'[Enquiry Source] ) = "Verso Group",
    COUNTROWS ( FILTER ( 'Lead', 'Lead'[Enquiry Source] = "Verso Group" && 'Lead'[statecode] = 2 && 'Lead'[Disqualified Reason] = "Cannot Contact" || 'Lead'[Disqualified Reason] = "Not Interest" || 'Lead'[Disqualified Reason] = "Wrong Number" )), 
    COUNTROWS ( FILTER ( 'Lead', 'Lead'[Enquiry Source] <> "Verso Group" && 'Lead'[statecode] = 2 && 'Lead'[Disqualified Reason] = "Spam" || 'Lead'[Disqualified Reason] = "Duplicates") )
)

I want to count the total amount of disqualifed leads, but without counting Spam and Duplicates. Do you know what I'm doing wrong with this measure? I'm having no luck.

[v-lid-msft]

@michael_knight  ,

You can try to use the following measure, if the result is still wrong, Please show the exact expected result based on the Tables that you have shared.

Total Disqualifed No Spam & Dupe - VG = 
IF (
    SELECTEDVALUE ( 'Lead'[Enquiry Source] ) = "Verso Group",
    COUNTROWS (
        FILTER (
            'Lead',
            'Lead'[Enquiry Source] = "Verso Group"
                && 'Lead'[statecode] = 2
                && ('Lead'[Disqualified Reason] = "Cannot Contact"
                || 'Lead'[Disqualified Reason] = "Not Interest"
                || 'Lead'[Disqualified Reason] = "Wrong Number")
        )
    ),
    COUNTROWS (
        FILTER (
            'Lead',
            'Lead'[Enquiry Source] <> "Verso Group"
                && 'Lead'[statecode] = 2
                && ('Lead'[Disqualified Reason] = "Cannot Contact"
                || 'Lead'[Disqualified Reason] = "Not Interest"
                || 'Lead'[Disqualified Reason] = "Wrong Number")
        )
    )
)

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[michael_knight]

Legend, thanks for all that 

[michael_knight]

Here are the measures that I've used to get the qualification rate % 

[amitchandak]

visual level filter to exclude "Facebook" is not suitable for your requirement?

[michael_knight]

Not really, because I don't want to completely filter out Facebook from the visual as I still want to use it with the Facebook button