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

     

Mariusz's avatar
Mariusz
Community Champion
6 years ago

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

    Mariusz This worked!! Thank you.

     

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

    • Mariusz's avatar
      Mariusz
      Community Champion
      6 years ago

      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'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
        )
        )
        )
        ))