DAX Query Union Multiple Tables & Return Distinct2019 Community Moderator ElectionDynamic DAX Security Filter Using Lookup Table with Multiple ValuesPowerBI DAX function to count number of occurrences using DISTINCT (Countif)DAX Filter Table To Replace RelationshipDAX way to return summarised dataTrying to return the value of a particular cell based on the value of a cell in current table using LOOKUPVALUEADD static table in DaxWhat is the PowerBI/DAX query equivalent of this SQL windowed functionHow to concatenate multiple rows from an other table in DAX?Distinct count of items in a column except the one selected from slicer DAXDAX A table of multiple values was supplied where a single value was expected
Multi tool use
Welcoming 2019 Pi day: How to draw the letter π?
Is it normal that my co-workers at a fitness company criticize my food choices?
RegionDifference for Cylinder and Cuboid
Bash: What does "masking return values" mean?
Using "wallow" verb with object
Instead of Universal Basic Income, why not Universal Basic NEEDS?
Why doesn't the EU now just force the UK to choose between referendum and no-deal?
2D counterpart of std::array in C++17
Where is the 1/8 CR apprentice in Volo's Guide to Monsters?
How to answer questions about my characters?
Is Mortgage interest accrued after a December payment tax deductible?
Rejected in 4th interview round citing insufficient years of experience
Good allowance savings plan?
Why are there 40 737 Max planes in flight when they have been grounded as not airworthy?
Do I need life insurance if I can cover my own funeral costs?
What does it mean to make a bootable LiveUSB?
Make a transparent 448*448 image
What options are left, if Britain cannot decide?
Does this AnyDice function accurately calculate the number of ogres you make unconcious with three 4th-level castings of Sleep?
How to make healing in an exploration game interesting
How to generate globally unique ids for different tables of the same database?
Meaning of "SEVERA INDEOVI VAS" from 3rd Century slab
What is IP squat space
What is a good source for large tables on the properties of water?
DAX Query Union Multiple Tables & Return Distinct
2019 Community Moderator ElectionDynamic DAX Security Filter Using Lookup Table with Multiple ValuesPowerBI DAX function to count number of occurrences using DISTINCT (Countif)DAX Filter Table To Replace RelationshipDAX way to return summarised dataTrying to return the value of a particular cell based on the value of a cell in current table using LOOKUPVALUEADD static table in DaxWhat is the PowerBI/DAX query equivalent of this SQL windowed functionHow to concatenate multiple rows from an other table in DAX?Distinct count of items in a column except the one selected from slicer DAXDAX A table of multiple values was supplied where a single value was expected
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?
powerbi dax
asked Mar 6 at 18:38
emieemie
129112
add a comment |
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?
powerbi dax
asked Mar 6 at 18:38
emieemie
129112
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.
Mar 6 at 19:45
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
Mar 6 at 19:47
add a comment |
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?
powerbi dax
asked Mar 6 at 18:38
emieemie
129112
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?
powerbi dax
powerbi dax
asked Mar 6 at 18:38
emieemie
129112
asked Mar 6 at 18:38
emieemie
129112
asked Mar 6 at 18:38
emieemie
129112
asked Mar 6 at 18:38
emieemie
129112
asked Mar 6 at 18:38
emieemie
129112
129112
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.
Mar 6 at 19:45
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
Mar 6 at 19:47
add a comment |
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.
Mar 6 at 19:45
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
Mar 6 at 19:47
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.
Mar 6 at 19:45
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.
Mar 6 at 19:45
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
Mar 6 at 19:47
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
Mar 6 at 19:47
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Mar 6 at 19:59
Ryan B.Ryan B.
1,71311018
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55030060%2fdax-query-union-multiple-tables-return-distinct%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Mar 6 at 19:59
Ryan B.Ryan B.
1,71311018
add a comment |
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.
answered Mar 6 at 19:59
Ryan B.Ryan B.
1,71311018
add a comment |
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.
answered Mar 6 at 19:59
Ryan B.Ryan B.
1,71311018
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.
answered Mar 6 at 19:59
Ryan B.Ryan B.
1,71311018
answered Mar 6 at 19:59
Ryan B.Ryan B.
1,71311018
answered Mar 6 at 19:59
Ryan B.Ryan B.
1,71311018
answered Mar 6 at 19:59
Ryan B.Ryan B.
1,71311018
1,71311018
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55030060%2fdax-query-union-multiple-tables-return-distinct%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Jm4mYfaKrXl4ZS Vv5 q,7C,5W9KOveJykdxl qbMB823fOK 8,bz,lJw g4zc3zKDPLc
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.
Mar 6 at 19:45
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
Mar 6 at 19:47