cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Nantes44100 Regular Visitor
Regular 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

I have book! Learn Power BI from Packt


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

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

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])

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Nantes44100 Regular Visitor
Regular 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 Regular Visitor
Regular 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 Regular Visitor
Regular 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

 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 334 members 3,433 guests
Please welcome our newest community members: