Desktop

[Nantes44100]

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!

[Greg_Deckler · Accepted solution]

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

[themistoklis]

@Nantes44100

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

Mainly from the first bullet point

[Greg_Deckler · Accepted solution]

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

[Greg_Deckler]

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

[Nantes44100]

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

Thanks @Greg_Deckler !

it's all good ! you rock!

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

[Nantes44100]

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