cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Frequency Count

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

1 ACCEPTED SOLUTION
Super User

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

0

1

2

3

4

...

active plans.  Would that be acceptable?

4 REPLIES 4
Super User

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

0

1

2

3

4

...

active plans.  Would that be acceptable?

Anonymous
Not applicable

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?

Super User

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?

Anonymous
Not applicable

Ahhh!! That makes sense.

I will try.

Thank you very much.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors