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

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's avatar
HenryJS
Post Prodigy
6 years ago

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

    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