Reply
Frequent Visitor
Posts: 13
Registered: ‎07-26-2018
Accepted Solution

NOT EXISTS in DAX

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.

 

Here is my file 

 

 

MANY THANKS!

 


Accepted Solutions
Super User
Posts: 10,508
Registered: ‎07-11-2015

Re: NOT EXISTS in DAX

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!


View solution in original post


All Replies
New Contributor
Posts: 420
Registered: ‎04-14-2018

Re: NOT EXISTS in DAX

@Nantes44100

 

Can you please explain the logic of the calculation that you want?

Mainly from the first bullet point

Super User
Posts: 10,508
Registered: ‎07-11-2015

Re: NOT EXISTS in DAX

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!


Super User
Posts: 10,508
Registered: ‎07-11-2015

Re: NOT EXISTS in DAX

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!


Frequent Visitor
Posts: 13
Registered: ‎07-26-2018

Re: NOT EXISTS in DAX

@themistoklis @Greg_Deckler

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.

 

 

Frequent Visitor
Posts: 13
Registered: ‎07-26-2018

Re: NOT EXISTS in DAX

[ Edited ]

 

Thanks @Greg_Deckler !

 

it's all good ! you rock!

 

I have replaced COUNTX by SUMX to sum data[HOW_MANY_JOB]

 

 

Highlighted
Frequent Visitor
Posts: 13
Registered: ‎07-26-2018

Re: NOT EXISTS in DAX

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 Smiley Wink