- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
NOT EXISTS in DAX
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-17-2019 05:35 AM
Hi, here is my situation (that I have simplified for this post)
I have a table with jobs offers
A job offer can be posted on different website (A,B,OTHER or Not Available)
Each offer jobs can propose 1 or more vacancy
Here are 2 DAX Measures I'm trying to do :
1) how many jobs the website B did not propose at all
2) how many jobs were posted only by the website B
I have duplicates in my table due to the publication column, so I struggle to do it in DAX.
I'm looking for to do like in SQL with NOT EXISTS or NOT IN.
MANY THANKS!
Solved! Go to Solution.
Accepted Solutions
Re: NOT EXISTS in DAX
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-17-2019 05:57 AM
For the first part, is this what you are going for?
Measure = VAR __all = COUNTX(DISTINCT(ALL(data[JOB_NAME])),[JOB_NAME]) VAR __b = COUNTX(DISTINCT(FILTER(ALL(data),[WEBSITE_SOURCE]="B")),[JOB_NAME]) RETURN __all - __b
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
All Replies
Re: NOT EXISTS in DAX
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-17-2019 05:52 AM
Can you please explain the logic of the calculation that you want?
Mainly from the first bullet point
Re: NOT EXISTS in DAX
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-17-2019 05:57 AM
For the first part, is this what you are going for?
Measure = VAR __all = COUNTX(DISTINCT(ALL(data[JOB_NAME])),[JOB_NAME]) VAR __b = COUNTX(DISTINCT(FILTER(ALL(data),[WEBSITE_SOURCE]="B")),[JOB_NAME]) RETURN __all - __b
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
Re: NOT EXISTS in DAX
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-17-2019 06:03 AM
For the second one, perhaps this:
Measure 2 = VAR __table = FILTER(data,[WEBSITE_SOURCE] = "B") VAR __table1 = ADDCOLUMNS(__table,"__num",COUNTX(FILTER(ALL(data),[JOB_NAME]=EARLIER([JOB_NAME])),[WEBSITE_SOURCE])) RETURN COUNTX(FILTER(__table1,[__num]=1),[JOB_NAME])
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
Re: NOT EXISTS in DAX
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-17-2019 06:04 AM
Sure. My client B wants to be the leader in Jobs Offering. And he wants to know his position towards his competitors
In my first calculation, I want to express how many jobs offers are posted on his competitors websites that are not posted by my client.
In my dataset, I can know for an ID if the job has been posted and where.
Re: NOT EXISTS in DAX
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-17-2019 07:09 AM - edited 01-17-2019 07:09 AM
Thanks @Greg_Deckler !
it's all good ! you rock!
I have replaced COUNTX by SUMX to sum data[HOW_MANY_JOB]
Re: NOT EXISTS in DAX
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-18-2019 12:47 AM
Hi,
sorry I talked too fast. the 2nd measure was not working (the one to calculate how many jobs posted on website B only)
Actually with my simple dataset example, it was because "Accountant" has one line and by chance the source was B
When I changed Accountant by butcher, my measure was Blank
I use a filter on the field ID, and it is working properly.
So, so far here is what I have :
#JOBS NOT POSTED BY B AT ALL = VAR __all = sumx(SUMMARIZE(data;data[ID];data[JOB_NAME];data[HOW_MANY_JOB]);data[HOW_MANY_JOB]) var __b = sumx(SUMMARIZE(FILTER(data;data[WEBSITE_SOURCE]="B");data[ID];data[JOB_NAME];data[HOW_MANY_JOB]);data[HOW_MANY_JOB]) return __all - __b
#JOB POSTED BY B ONLY = var __table = filter(data;data[WEBSITE_SOURCE]="B") var __table1 = ADDCOLUMNS(__table;"__num";COUNTX(filter(ALL(data);data[ID]=EARLIER(data[ID]));data[WEBSITE_SOURCE])) return SUMX(filter(__table1;[__num]=1);data[HOW_MANY_JOB])
I am going to use this with my real dataset, which is more complex