Add Measure Count Across Two Tables

[HenryJS]

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

'Export Adverts'[Mailshots]<=0.9

|| 'Export Adverts'[Mailshots] <> BLANK())

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

[v-shex-msft]

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

[Mariusz]

Hi @HenryJS 

Or try this as another option 

Without Reach = 
SUMX( 
    VALUES( 'Jobs'[JobRef] ),
    CALCULATE(
        INT(
            ISEMPTY(
                FILTER(
                    'Export Adverts',
                    'Export Adverts'[Mailshots] >= 1
                )
            )
        )
    )
)

[HenryJS]

@Mariusz This worked!! Thank you.

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

[Mariusz]

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.
LinkedIn

[HenryJS]

@Mariusz Hi

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

Without Reach =

SUMX(

VALUES( 'Jobs'[JobRef] ),

CALCULATE(

INT(

ISEMPTY(

FILTER(

'Export Adverts',

'Export Adverts'[Mailshots] >= 1

)

)

)

))

[Mariusz · Accepted 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
                )
            )
        )
    )
)

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn

[ibarrau]

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,

[HenryJS]

Hi @ibarrau @Mariusz 

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

[HenryJS]

@ibarrau @Mariusz 

Tried this but it didn't work

Without Reach = CALCULATE(CROSSFILTER('Export Adverts'[Job Ref],JobActions[JobRef],Both),'Export Adverts'[Mailshots]<1 || ISBLANK('Export Adverts'[Mailshots])), DISTINCTCOUNT(Jobs[JobRef])

[Mariusz]

Hi @HenryJS 

Try this.

CALCULATE(
DISTINCTCOUNT(Jobs[JobRef]),
CROSSFILTER('Export Adverts'[Job Ref],JobActions[JobRef],Both),
'Export Adverts'[Mailshots]<1 || ISBLANK('Export Adverts'[Mailshots])
)

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn

[Mariusz]

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.
LinkedIn