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

     

ibarrau's avatar
ibarrau
Super User
6 years ago

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