cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Prodigy

## Add Measure Count Across Two Tables

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

Without Reach = CALCULATE(
DISTINCTCOUNT('Jobs'[JobRef]),

But its not working I think it is something to do with filters across two tables?

1 ACCEPTED SOLUTION
Community Champion

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(
)
)
)
)
)``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

11 REPLIES 11
Community Support

HI @HenryJS,

You can try to use the following measure formula if it meets for your requirement:

``````Without Reach =
VAR filtered =
CALCULATETABLE (
FILTER (
|| '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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Community Champion

Hi @HenryJS

Or try this as another option

``````Without Reach =
SUMX(
VALUES( 'Jobs'[JobRef] ),
CALCULATE(
INT(
ISEMPTY(
FILTER(
)
)
)
)
)``````

Post Prodigy

@Mariusz This worked!! Thank you.

Now I want to add to that query IF 'Jobs'[Offers] <1

Community Champion

Hi @HenryJS

I'm glad it's working for you but which version are you using, as I posted two?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Post Prodigy

@Mariusz Hi

Below measure worked. Need to add 'Jobs'[Offers]<1 if possible?

Without Reach =
SUMX(
VALUES( 'Jobs'[JobRef] ),
CALCULATE(
INT(
ISEMPTY(
FILTER(
)
)
)
))
Community Champion

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(
)
)
)
)
)``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Super User

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

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

Post Prodigy

I need a measure which calculates:

• Distinct Count of 'Jobs'[Jobref] WHEN
• The Jobref in 'Export Adverts' has 'Export Adverts'[Mailshots] <1 OR 0 or BLANK
• OR The Jobref is not present in 'Export Adverts'

Can this be done?

Thank you

Post Prodigy

Tried this but it didn't work

Community Champion

Hi @HenryJS

Try this.

``````CALCULATE(
DISTINCTCOUNT(Jobs[JobRef]),
)``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Community Champion

Hi @HenryJS

You can add CROSSFILTER to your calculate, with Both as the third argument.

``CROSSFILTER( 'Export Adverts'[Key], Jobs[Key], Both )``

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.