"Countif" across two tables in Direct Query

[JasonG-BI]

Hi All,

Im really stuck on something that should be so simple.

I am trying to Count the Number of Sales Executives that have sales more than Zero. Which is accurate with applied slicers such as Month year and Store. So that we can have a sales revenue divided by Sales Execs.  The sales execs name is in one Table and the Revenue is in another. Im really struggling with the count function of the sales person.

The name is in 'Sales Name'[Sales Exec]

and the Sales Revenue is in  'Sales Revenue'[Revenue_Awarded]

Any help is much appreciated.

 

Im in directy query and the two tables are linked.

[Zubair_Muhammad · Accepted solution]

Hi @JasonG-BI

Try this MEASURE

MEASURE =
CALCULATE (
    COUNT ( 'Sales Name'[Sales Exec] ),
    FILTER (
        ALL ( 'Sales Name'[Sales Exec] ),
        CALCULATE ( SUM ( 'Sales Revenue'[Revenue_Awarded] ) ) > 0
    )
)

[JasonG-BI]

Thank you for your reply however this doesnt appear to work in direct query.

I get the error message "Function 'Filter' is not supported in this context in DirectQuery mode.

Is there another way??

[Zubair_Muhammad]

Hi @JasonG-BI

Did you add it as a MEASURE or Calculated Column?

[Zubair_Muhammad]

@JasonG-BI

here is another way

MEASURE =
COUNTROWS (
    FILTER (
        ALL ( 'Sales Name'[Sales Exec] ),
        CALCULATE ( SUM ( 'Sales Revenue'[Revenue_Awarded] ) ) > 0
    )
)

[JasonG-BI]

thank you this is now working with the original solution i just needed to enable a setting in option direct query. This is a massive help. Thanks !!!!