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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors