Forum Discussion

HenryJS's avatar
HenryJS
Post Prodigy
6 years ago
Solved

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'[Mailshot...
Data Protection
Need Help
Show and Tell
Tips and Tricks
  • Mariusz's avatar
    Mariusz
    6 years ago

    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

     

HenryJS's avatar
HenryJS
Post Prodigy
6 years ago

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's avatar
Mariusz
Community Champion
6 years ago

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