If you are in Marketing and trying to understand the productivity of various sources of Leads that do or do not convert to Opportunities for Sales, using a tool like Power BI for that analysis can be a great choice.
But knowing how to use filter arguments in a CALCULATE statement to build your KPIs/measures can sometimes be tricky.
Let’s say your organization has created 1,000 leads across multiple lead sources in the last 30 days that you want to segment and investigate.
These are your lead sources:
- Channel Partners
Let’s create a measure that “counts all leads”, as follows:
.Leads = SUM(Leads[leadcount])
Create a card to see the result, as you would expect:
Note that there is only 1 filter context for the measure to be evaluated here…you are counting rows in the Lead table with all records selected.
Now we change the card to a table and add LeadSource:
By doing this, we create 9 filter contexts where Power BI is evaluating the .Leads measure…once for each different value of LeadSource, where you count only rows selected for 1 value of LeadSource, and once for the grand total where you count rows in the Lead table with all records selected (as was the case with the card).
Now we want to create a measure that only counts leads from “non-social sources”.
So we write the following measure using CALCULATE:
.Leads_NonSocial = CALCULATE(‘Leads'[.Leads],Leads[LeadSource]<>”Facebook” && Leads[LeadSource]<>”LinkedIn” && Leads[LeadSource]<>”Twitter”)
Create a card. Looks good…the total is 625, which is what we would expect:
Add that measure to our table and…uh oh:
That’s not what we were expecting.
What are we seeing? It’s as if Power BI was ignoring the filter context created by each different value of LeadSource, and instead computing the count of ALL Leads, except from our non-social lead sources, for each of the first 8 filter contexts.
It’s as if Power BI was evaluating a measure written like this:
.Leads_NonSocial = CALCULATE([.Leads],FILTER(ALL(Leads[LeadSource]),Leads[LeadSource]<>”Facebook” && Leads[LeadSource]<>”Twitter” && Leads[LeadSource]<>”LinkedIn”))
In fact, that’s exactly what Power BI is doing when your filter arguments to CALCULATE are written as “boolean” arguments without explicitly using the FILTER function.
What we wanted was for Power BI to respect the current filter context created by each different value of LeadSource and then apply the filter arguments. You want the values of LeadSource in the each of the first 8 filter contexts to hold before CALCULATE modifies that filter context with the filter arguments.
The solution is to rewrite our measure as follows:
.Leads_NonSocial_alt = CALCULATE([.Leads],FILTER(VALUES(Leads[LeadSource]),Leads[LeadSource]<>”Facebook” && Leads[LeadSource]<>”Twitter” && Leads[LeadSource]<>”LinkedIn”))
Now look at our table again, and you’ll see the result we had wanted in the first place:
Conclusion: You need to remember that passing boolean filter arguments to CALCULATE is the same as passing FILTER(ALL([column name])),[filter arguments]). Or forget trying to remember that rule and get in the habit of explicitly writing your filter arguments to CALCULATE as either FILTER(ALL()) or FILTER(VALUES()) instead. If FILTER(ALL()) doesn’t get you the result you want, try FILTER(VALUES()). You’ll have a 50/50 chance of being right the first time!