Question on filtered measures

[Anonymous]

Hello all,

I'm trying to create a measure that calculates the count filtered by a column with three different criteria. I created measures if I'm filtering by 1 criteria (see below), but need to filter by an additional three criteria within the same column. For example, I need to calculate the # of leads if they have a status of "Qualified" AND if their lead source is Marketing Event, HubSpot - Leadscore, or HubSpot - Talk-to-Sales. I'm getting an error when trying to add additional columns. Any help would be greatly appreciated. Thank you!

[Anonymous · Accepted solution]

Nevermind, I was able to validate it. I got the same number with the below measure using the format in the article you shared. Thank you!

[Anonymous]

In case this is also helpful, this is what I'm seeing:

[Anonymous]

Thank you, @OwenAuger !

I have multiple measures I need to create and will use the above you provided. However, I'm seeing a discrepancy when validating these measures. 

I'm getting 1,376 leads when using the below measure:

I'm using this measure and slicer filter for the column "lead_source" to check the above. I'm getting a discrepancy of 11 contacts. Do you know why this might be?

[OwenAuger]

Hi @Anonymous 

From what you've described, this is how I would write the updated measure:

# sales qualified leads modified =
CALCULATE (
    DISTINCTCOUNT ( dv_lead_to_opty[lead_id] ),
    dv_lead_to_opty[lead_status] = "Qualified",
    dv_lead_to_opty[lead_source]
        IN { "Marketing Event", "HubSpot - Leadscore", "Hubspot - Talk-to-Sales" }
)

Generally it's best practice to include multiple filters with "and" conditions as separate arguments within CALCULATE.

See this article for some examples/discussion:

https://www.sqlbi.com/articles/specifying-multiple-filter-conditions-in-calculate/

Does something like the above measure give the expected result?

Regards