topic Re: Frequency Count in DAX Commands and Tips

Frequency Count

Anonymous — Wed, 22 Jul 2020 13:01:46 GMT

Hi Team

Its been bothering me for a while and I believe answer is simple and striaght forward. But brain has gone flat.

I want to show how many "Active" Plans each customer has. Not fussed about date/time. I believe the relationship to Dim Calendar will take care of it.

This is how the data is -

ConsumerID	PlanID	Status	StatusDate
1	123	Active	12/01/2020
1	345	Active	19/05/2020
1	567	Closed	12/07/2020
2	234	Created	10/06/2020
2	456	Cancelled	12/06/2020
2	987	Created	15/05/2020
3	765	Active	16/05/2020
3	543	Default	01/07/2020
4	321	Active	11/07/2020

I want result as

Number of Plans	Number of Customers
0	1(customerid 2)*
1	2(customer id 3 & 4)*
2	1(customer id 1)*

* The info in bracket is to show you, not what I need in output. 🙂

Any help is greatly appreciated.

Cheers

Re: Frequency Count

lbendlin — Wed, 22 Jul 2020 14:15:49 GMT

If I read your expected outcome then you don't want to show how many "Active" Plans each customer has. What you are asking for is a bucketing of how many customers a group of plans has, sort of a double histogram. Can you please clarify?

Anyway, here's the first step. Number of active plans per customer.

Next step is to create a measure table to do the grouping, and hopefully also be able to list the customer IDs as a bonus

Question: It would be much simpler if one could start with a static list of

...

active plans. Would that be acceptable?

Re: Frequency Count

Anonymous — Wed, 22 Jul 2020 23:16:42 GMT

lbendlin

Thanks. That is another way of looking at it - "how many customers a group of plans has"

I see where you are going. though couple of things -

1) I dont need to show the Customer ID column, I need to show a count

2) Static List

2.1) "may" be possible - but there is no known upper limit. It could be about million plans

2.2) what am I joining it to? As in what are you suggesting to use this for?

Re: Frequency Count

lbendlin — Wed, 22 Jul 2020 23:36:53 GMT

2.1 is not about the number of plans, it is about the unique number of plans a group of customers may have. As I said, a double histogram (bucket in a bucket). Even if you have a million customers the number of plans each of them has must be much smaller, no?

Re: Frequency Count

Anonymous — Thu, 23 Jul 2020 04:24:59 GMT

Ahhh!! That makes sense.

I will try.

Thank you very much.