cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nantes44100 Frequent Visitor
Frequent Visitor

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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!


6 REPLIES 6
themistoklis New Contributor
New Contributor

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
Super User

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!


Highlighted
Super User
Super User

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!


Nantes44100 Frequent Visitor
Frequent Visitor

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.

 

 

Nantes44100 Frequent Visitor
Frequent Visitor

Re: NOT EXISTS in DAX

 

Thanks @Greg_Deckler !

 

it's all good ! you rock!

 

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

 

 

Nantes44100 Frequent Visitor
Frequent Visitor

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