Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
MANY THANKS!
Solved! Go to 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
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 😉
Thanks @Greg_Deckler !
it's all good ! you rock!
I have replaced COUNTX by SUMX to sum data[HOW_MANY_JOB]
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.
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])
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
@Anonymous
Can you please explain the logic of the calculation that you want?
Mainly from the first bullet point
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |