Help with DAX - Union measures with no overlap

[aashton]

Hello,

My report has a table Leads, including Email, Name, Lead Type.  This links to Applications, with fields Name, Email, App ID, job, etc. many-to-many on email.  I need to find Total Applications for source Events, which would include:  All those applications for emails from the Leads table with a Lead Type of Event, plus any applications from the Applicaitons table that have a source of Events and are not included in the Leads table (so no overlaps).  

To find applications for those with a Lead of Source I use:  

Total Applications Events 2 = CALCULATE([Total Applications No blank email 2], 'Lead Types Many'[Lead Type] IN {"Events"} && NOT(ISBLANK('Lead Types Many'[Email])))

To find Applications that have a source of Events, I use:  

Total Applications Events Source = CALCULATE([Total Applications No blank email 2], 'Application Raw Data (2)'[Source]="Events")

Unsure how to put those together and not count the same person twice.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result very clearly.

[123abc]

To achieve your desired outcome of finding the total applications for leads with a Lead Type of "Events" and also applications from the Applications table with a source of "Events" but without counting the same person twice, you can use DAX in Power BI. You can create a measure that combines these two conditions. Here's how you can do it:

First, create a measure for Total Applications for Leads with a Lead Type of "Events":

Total Applications Events from Leads =
CALCULATE(
[Total Applications No blank email 2],
'Lead Types Many'[Lead Type] = "Events" && NOT(ISBLANK('Lead Types Many'[Email]))
)

Next, create a measure for Total Applications for Applications with a source of "Events":

Total Applications Events from Applications =
CALCULATE(
[Total Applications No blank email 2],
'Application Raw Data (2)'[Source] = "Events"
)

Now, you can create the final measure that combines these two measures and ensures that the same person is not counted twice:

Total Applications Events Combined =
VAR TotalLeadsApplications = [Total Applications Events from Leads]
VAR TotalApplications = [Total Applications Events from Applications]

RETURN
TotalLeadsApplications + TotalApplications - CALCULATE(
[Total Applications No blank email 2],
'Lead Types Many'[Lead Type] = "Events" &&
NOT(ISBLANK('Lead Types Many'[Email])) &&
'Application Raw Data (2)'[Source] = "Events"
)

This measure calculates the total applications for leads with a Lead Type of "Events," the total applications for applications with a source of "Events," and then subtracts the count of applications that meet both conditions to avoid double-counting.

Now you can use the "Total Applications Events Combined" measure in your Power BI report to get the desired result.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[aashton]

@123abc  I tried revising to:  

Total Applications Events Lead and Source = CALCULATE([Total Applications No blank email 2], FILTER('Lead Types Many', 'Lead Types Many'[Lead Type]="Events" && NOT(ISBLANK('Lead Types Many'[Email])) && RELATED('Application Raw Data (2)'[Source]) = "Events")).....

But am now getting an error saying there is no relationship between the tables, but there is:

[aashton]

@123abc Thank you so much for your help, but for the combined measure, it is not letting me pick fields from 2 different tables: