Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi all,
I have to tables: Jobs and Export Adverts
They are joined on 'Jobref' Columns
I want to create a measure which counts 'Jobs'[Jobref] when that Jobref has 'Export Adverts'[Mailshots] <1 or blank
I created the below measure
But its not working I think it is something to do with filters across two tables?
Solved! Go to Solution.
Hi @HenryJS
Try this
Without Reach =
VAR __JobRef =
CALCULATETABLE(
VALUES( 'Jobs'[JobRef] ),
FILTER(
ALL( 'Jobs'[Offers] ),
'Jobs'[Offers] < 1
)
)
RETURN
SUMX(
__JobRef,
CALCULATE(
INT(
ISEMPTY(
FILTER(
'Export Adverts',
'Export Adverts'[Mailshots] >= 1
)
)
)
)
)
HI @HenryJS,
You can try to use the following measure formula if it meets for your requirement:
Without Reach =
VAR filtered =
CALCULATETABLE (
VALUES ( 'Export Adverts'[Advert Ref] ),
FILTER (
ALLSELECTED ( 'Export Adverts' ),
'Export Adverts'[Mailshots] <= 0.9
|| 'Export Adverts'[Mailshots] <> BLANK ()
)
)
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Jobs'[JobRef] ),
FILTER ( ALLSELECTED ( 'Jobs' ), 'Jobs'[AdvertRef] IN filtered )
)
If above not helps, please share some dummy data with formulas that used in your calculation to help us clarify your scenario and test on it.
Regards,
Xiaoxin Sheng
Hi @HenryJS
Or try this as another option
Without Reach =
SUMX(
VALUES( 'Jobs'[JobRef] ),
CALCULATE(
INT(
ISEMPTY(
FILTER(
'Export Adverts',
'Export Adverts'[Mailshots] >= 1
)
)
)
)
)
@Mariusz Hi
Below measure worked. Need to add 'Jobs'[Offers]<1 if possible?
Hi @HenryJS
Try this
Without Reach =
VAR __JobRef =
CALCULATETABLE(
VALUES( 'Jobs'[JobRef] ),
FILTER(
ALL( 'Jobs'[Offers] ),
'Jobs'[Offers] < 1
)
)
RETURN
SUMX(
__JobRef,
CALCULATE(
INT(
ISEMPTY(
FILTER(
'Export Adverts',
'Export Adverts'[Mailshots] >= 1
)
)
)
)
)
Hi. I may be wrong but you mention you want to know if its < 1 or blank. Your formula is asking for values <> blank and not = blank.
Don't you want blank values or < 1?
Without Reach = CALCULATE(
DISTINCTCOUNT('Jobs'[JobRef]),
'Export Adverts'[Mailshots]<=0.9
|| ISBLANK( 'Export Adverts'[Mailshots]
)
Regards,
Happy to help!
Hi @HenryJS
Try this.
CALCULATE(
DISTINCTCOUNT(Jobs[JobRef]),
CROSSFILTER('Export Adverts'[Job Ref],JobActions[JobRef],Both),
'Export Adverts'[Mailshots]<1 || ISBLANK('Export Adverts'[Mailshots])
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
86 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
112 | |
99 | |
69 | |
67 |