cancel
Showing results for
Did you mean:
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

## 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

Proud to be a Datanaut!

6 REPLIES 6
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

## 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

Proud to be a Datanaut!

Super User

## Re: NOT EXISTS in DAX

For the second one, perhaps this:

Measure 2 =
VAR __table = FILTER(data,[WEBSITE_SOURCE] = "B")
RETURN
COUNTX(FILTER(__table1,[__num]=1),[JOB_NAME])

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Regular Visitor

## Re: NOT EXISTS in DAX

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.

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]

Highlighted
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")
return
SUMX(filter(__table1;[__num]=1);data[HOW_MANY_JOB])

I am going to use this with my real dataset, which is more complex

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### 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.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 365 members 4,005 guests
Recent signins: