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'[Mailshots] <1 or blank

 

I created the below measure

 

Without Reach = CALCULATE(
DISTINCTCOUNT('Jobs'[JobRef]),
'Export Adverts'[Mailshots]<=0.9
|| 'Export Adverts'[Mailshots] <> BLANK())

 

 

But its not working I think it is something to do with filters across two tables?

 

 

 

 

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

     

11 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi HenryJS 

     

    You can add CROSSFILTER to your calculate, with Both as the third argument.

    CROSSFILTER( 'Export Adverts'[Key], Jobs[Key], Both )

     

    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 

     

    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

         

  • 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