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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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