topic Re: Calculate distinct customer-Id per day in DAX Commands and Tips

Calculate distinct customer-Id per day

clarissa — Sat, 13 Jun 2020 07:49:15 GMT

Hello

I have a table with following schema:

Date Customer ID

13.1.2020 A

13.1.2020 B

20.1.2020 C

1.2.2020 B

14.2.2020 A

I need to count the distinct Customer-ID per day which serves as the number of orders, so the result would be

A= 2

B= 2

C=1

This column "number of orders" should sum up over a filtered time. I tried it with this formula, but the sum of the counted rows per month doesn't show the right total:

Number of Orders =

Var NumberOfOrders = CALCULATE(DISTINCTCOUNT('Mart DimCustomer'[Customer_ID]); ALLSELECTED('Mart FactInvoice'[date]))

Return CALCULATE(NumberOfOrders)

Any recommendations how to solve this? Thank you!

Re: Calculate distinct customer-Id per day

lbendlin — Sat, 13 Jun 2020 10:26:16 GMT

You'll want to have different calculations depending if you are in a "row" or in the total. Use HASONEVALUE() to differentiate.

Re: Calculate distinct customer-Id per day

Anonymous — Sat, 13 Jun 2020 11:22:52 GMT

Hi clarissa ,

Create a measure

Distinct Count of Customer Day = COUNTROWS(SUMMARIZE('Table','Table'[Date]))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Calculate distinct customer-Id per day

clarissa — Sat, 13 Jun 2020 13:01:16 GMT

Hi Harsh

Thank you for your feedback. Unfortunately the number shows way too less orders when I calculate it this way... I only get 12 orders per month April although it should be much more.... Could it be that this formula only counts distinct customer_ID per filtered time frame? So if a customer orders 4x in April and I filter April, that I only get 1x the customer?

I would need every customer ID per day as 1 count and if that customer ID appears 2x or 3x it should count it only 1x per day..

Was it clear what I try to achieve?

Thanks again for your help and best regards, Clarissa

Re: Calculate distinct customer-Id per day

mahoneypat — Sun, 14 Jun 2020 01:17:16 GMT

When you tried the expression by Anonymous did you visual have Customer ID in it? In any case, if you modify that expression as follows, it should work whether it is or not.

Distinct Count of Customer Day = COUNTROWS(SUMMARIZE('Table','Table'[Date], 'Table'[customer_ID]))

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Calculate distinct customer-Id per day

clarissa — Sun, 14 Jun 2020 07:54:27 GMT

thank you Pat.

Can you explain me, what this Summary function with the 2 groups (date, ID) actually does?

and why does this formula not work?

CALCULATE(DISTINCTCOUNT('Tabe'[Customer_ID]))

Would it just count the distinct customer IDs in the time period I would filter in the table? so if the time period changes it would also change the count to the new time period and only count distinct IDs?

Thank you!

Re: Calculate distinct customer-Id per day

stevedep — Sun, 14 Jun 2020 08:12:32 GMT

Hi,

Try SUMX(disctinctcount ...

You need an interator to sum subtotals.

Kind regards, Steve.

Re: Calculate distinct customer-Id per day

clarissa — Sun, 14 Jun 2020 11:38:57 GMT

Hi Steve,

thank you. I tried it with your proposal:

Count Orders = SUMX('Mart FactInvoice'; (DISTINCTCOUNT('Mart FactInvoice'[CustomerID])))

but it counts several duplicate customer IDs for the same day:

Best regards

Clarissa

Re: Calculate distinct customer-Id per day

stevedep — Sun, 14 Jun 2020 12:49:58 GMT

Perhaps something more explicit:

Sumx(values(table[date], distinctcount..

Re: Calculate distinct customer-Id per day

clarissa — Sun, 14 Jun 2020 13:35:08 GMT

Hi Steve

so you would write it like this?

Count Orders = SUMX(VALUES('Mart FactInvoice'[Date]);DISTINCTCOUNT('Mart FactInvoice'[customer-id]))

Re: Calculate distinct customer-Id per day

stevedep — Sun, 14 Jun 2020 13:36:44 GMT

Yes, if this doesn't work we need to know the data model.

Re: Calculate distinct customer-Id per day

clarissa — Sun, 14 Jun 2020 14:02:03 GMT

Hi Steve

Unfortunately it doesnt work.

The data model:

- 1 table factinvoice with revenues and invoice dates (*)

- 1 table with customer (1)

- join through customer_id: 1 to *

Does that help? Thanks for your support!