Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

"Countif" across two tables in Direct Query

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.

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Anonymous

     

    Try this MEASURE

     

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

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi Anonymous

     

    Try this MEASURE

     

    MEASURE =
CALCULATE (
    COUNT ( 'Sales Name'[Sales Exec] ),
    FILTER (
        ALL ( 'Sales Name'[Sales Exec] ),
        CALCULATE ( SUM ( 'Sales Revenue'[Revenue_Awarded] ) ) > 0
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Hi Anonymous

         

        Did you add it as a MEASURE or Calculated Column?