Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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 -

 

ConsumerIDPlanIDStatusStatusDate
1123Active12/01/2020
1345Active19/05/2020
1567Closed12/07/2020
2234Created10/06/2020
2456Cancelled12/06/2020
2987Created15/05/2020
3765Active16/05/2020
3543Default01/07/2020
4321Active11/07/2020

 

I want result as 

Number of PlansNumber of Customers
01(customerid 2)*
12(customer id 3 & 4)*
21(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
lbendlin
Super User
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. 

 

lbendlin_0-1595426564742.png

 

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?

View solution in original post

4 REPLIES 4
lbendlin
Super User
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. 

 

lbendlin_0-1595426564742.png

 

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

@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?

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.