Forum Discussion

clarissa's avatar
clarissa
Icon for Helper I rankHelper 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). Th...
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)

 

 

clarissa's avatar
clarissa
Icon for Helper I rankHelper I
6 years ago

Hi Harsh

 

Thank you for your feedback. Why do you think it is linked to the trailing spaces? I get numbers but the Google Analytics number is higher than the Sales Table Number, which doesn't make sense... Is the DAX Formula right or does it react differently if the distinctcount is applied on another table than the filter table?`

 

Thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi clarissa ,

     

    Can you share a sample .pbix file.

     

    Regards,

    HN

    • clarissa's avatar
      clarissa
      Icon for Helper I rankHelper I
      6 years ago

      Hi Anonymous 

      Unfortunately not, but here the data modell

       

       

      Does that help? B2C_unique_order_number is joined with the 2 other tables with the order-no (1 to *)