DAX Query Union Multiple Tables & Return Distinct

Question

I have two tables (CompletedJobs & ScriptDetails) and using DAX, I want to return distinct Names that appear in CompletedJobs that do not appear in ScriptDetails.

Here is my SQL Query. Works and return values.

Select Distinct CJ.[Name]
From CompletedJobs CJ
Left Join ScriptDetails SD
ON CJ.[Name]=SD.ActivityName
Where SD.ActivityName IS NULL

I started with creating the following DAX query, but just doing this, I get the following error message:

"A table of multiple values was supplied where a single value was expected"

AdhocJobs = DISTINCT(UNION(SELECTCOLUMNS(CompletedJobs,"Name",CompletedJobs[Name]),SELECTCOLUMNS(ScriptDetails,"Name",ScriptDetails[ActivityName])))

How do I create a DAX query that would replicate the SQL query?

This query makes no sense. Why join to ScriptDetails when you don't use any columns from that table, and the element you're joining on is restricted to NULL, and the only values you're showing are part of the join (which is NULL)? — Ryan B.
I accidentally delete the columns from ScriptDetails. Here is the revised query. Select CJ.[Name], JS.Total_PN_hours, JS.Bonus_PN_hours From CompletedJobs CJ Left Join ScriptDetails SD ON CJ.[Name]=SD.ActivityName Inner Join JobStatistics JS ON JS.JobId = CJ.ID Where SD.ActivityName IS NULL — emie

Ryan B. Ryan B. · Accepted Answer · 2019-03-06T19:59:57

Rather than recreate your SQL, there is DAX that already addresses your specific use case. The EXCEPT function returns a table where rows from the LEFT SIDE table do not appear in the RIGHT SIDE table.

EVALUATE
DISTINCT (
    EXCEPT (
        SUMMARIZE ( CompletedJobs , CompletedJobs [Name]),
        SUMMARIZE ( ScriptDetails , ScriptDetails [ActivityName])
    )
)

In this case I used SUMMARIZE to reduce each table down to one column, and then wrapped them with EXCEPT to take only the Names from Completed Jobs that aren't ActivityNames in ScriptDetails.

Hope it helps.

DAX Query Union Multiple Tables & Return Distinct

1 Answers