Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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])
)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
109 | |
108 | |
93 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
102 | |
86 |