Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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?
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?
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?
Ahhh!! That makes sense.
I will try.
Thank you very much.
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
87 | |
47 | |
30 | |
22 |