March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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])
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
91 | |
90 | |
80 | |
49 |
User | Count |
---|---|
160 | |
145 | |
103 | |
72 | |
55 |