Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
HenryJS
Post Prodigy
Post Prodigy

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 Relationship.JPG

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

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

 

View solution in original post

11 REPLIES 11
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Mariusz
Community Champion
Community Champion

Hi @HenryJS 

 

Or try this as another option 

Without Reach = 
SUMX( 
    VALUES( 'Jobs'[JobRef] ),
    CALCULATE(
        INT(
            ISEMPTY(
                FILTER(
                    'Export Adverts',
                    'Export Adverts'[Mailshots] >= 1
                )
            )
        )
    )
)

 

@Mariusz This worked!! Thank you.

 

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

Mariusz
Community Champion
Community Champion

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

 

@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
)
)
)
))
Mariusz
Community Champion
Community Champion

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
Super User
Super User

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

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

 

@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
Community Champion
Community Champion

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


 

Mariusz
Community Champion
Community Champion

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

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.