Forum Discussion

clarissa's avatar
clarissa
Helper I
6 years ago

distinct count with condition

Hello  How can I count distinct order_no which are only coming from specific traffic sources  (google, mail, bing)? So the distinctcount happens based on a pre-filtered data set (traffic sources). Thank you!

6 Replies

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    It sounds like a job for CALCULATE

    CALCULATE(DISTINCTCOUNT('Orders'[order_num]),'Orders'[source] IN { "google", "mail", "bing" })

    depending on the structure of your report and what the external filter context is you may have to add an ALL('Orders')

     

    Join the conversation on We Talk BI

    More about me on Slow BI

    • clarissa's avatar
      clarissa
      Helper I
      6 years ago

      Hi kentyler 

      Thanks for your answer.

       

      The data model is:

       

      - unique order number is in 1 table.

      - Google Analytics order number table ("google_analytics_order_no") is combined with this unique order number (1 to *)

      - ERP data ("sales") is combined with unique order number (1 to *). My formula looks like this:

      # Order PMKT = CALCULATE(DISTINCTCOUNT(sales[order_no]), FILTER(google_analytics_order_no, google_analytics_order_no[source_medium] = "google / cpc" || google_analytics_order_no[source_medium] = "bing / cpc" || google_analytics_order_no[source_medium] = "criteo / display" || google_analytics_order_no[source_medium] = "awin / affiliate" || google_analytics_order_no[source_medium] = "facebook / social_paid"))
       
      when I count the numbers of Google Analytics I have more orders, which doesnt make sense:


      # Order PMKT GA = CALCULATE(DISTINCTCOUNT(google_analytics_order_no[order_no]), FILTER(google_analytics_order_no, google_analytics_order_no[source_medium] = "google / cpc" || google_analytics_order_no[source_medium] = "bing / cpc" || google_analytics_order_no[source_medium] = "criteo / display" || google_analytics_order_no[source_medium] = "awin / affiliate" || google_analytics_order_no[source_medium] = "facebook / social_paid"))
       
      Does this formula with the filter not work in my datamodel?
       
      Thanks for your help, very much appreciated.
       
      Best regards
      Clarissa
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi clarissa ,

         

        Try trouble shooting your DAX

         

         

        Order PMKT = CALCULATE(DISTINCTCOUNT(sales[order_no]), FILTER(google_analytics_order_no, google_analytics_order_no[source_medium] = "google / cpc" )

        See if the formula is returning the correct value for "google/cpc".

         

        Also, remove all trailing spaces by Trimming this column in Query Editor.

         

        Regards,

        Harsh Nathani

        Appreciate with a Kudos!! (Click the Thumbs Up Button)