topic Re: distinct count with condition in DAX Commands and Tips

distinct count with condition

clarissa — Fri, 03 Jul 2020 14:12:29 GMT

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!

Re: distinct count with condition

kentyler — Fri, 03 Jul 2020 14:33:13 GMT

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

Re: distinct count with condition

clarissa — Mon, 06 Jul 2020 06:41:38 GMT

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

Re: distinct count with condition

Anonymous — Mon, 06 Jul 2020 09:09:47 GMT

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)

Re: distinct count with condition

clarissa — Mon, 06 Jul 2020 11:43:24 GMT

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!

Re: distinct count with condition

Anonymous — Mon, 06 Jul 2020 12:20:15 GMT

Hi clarissa ,

Can you share a sample .pbix file.

Regards,

Re: distinct count with condition

clarissa — Mon, 06 Jul 2020 12:53:19 GMT

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 *)