Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi, I am new to Power BI and need help on below.
I have data at date and custome level with channel type. I would need to have 2 period(date) paramenters and categorize customers based on selected dates in period parameters. Below is the sample data and expected output.
Customer | Date | Channel | Parameters | From | To | |||
A | 1-Oct | On | P1 | 1-Oct | 2-Oct | |||
A | 2-Oct | Off | P2 | 3-Oct | 3-Oct | |||
A | 3-Oct | Dir | ||||||
A | 4-Oct | On | Output | |||||
B | 1-Oct | On | P1 | P2 | Dir | Off | Total | |
B | 2-Oct | Off | On,Off | 1 | 1 | 2 | ||
B | 3-Oct | Off | Dir | 0 | 1 | 1 | ||
C | 2-Oct | Dir | Total | 1 | 2 | |||
C | 3-Oct | Off |
in the above example, Oct 1 - Oct 2 is selected for P1 (Period 1), so Customer A will fall into On,Off category,Customer B into On,Off and Customer C into Dir for P1. Similar calculation for P2 cateogries and then matrix of these 2 with count of customers. I am not able to find a way to create these calculations, any help is much appreciated.
Thanks in advance.
Hi @Anonymous ,
I can't think of a solution for applying dynamic categeory on column. They must be fixed in the formula based on my knowledge. Maybe someone else could give you a solution.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @Anonymous ,
Would you please try to create two calculated column :
P = SWITCH(TRUE(),'Table'[Date]>=DATE(2020,10,1)&&'Table'[Date]<=DATE(2020,10,2),"P1",'Table'[Date] = DATE(2020,10,3),"P2")
combinations = CONCATENATEX(FILTER('Table','Table'[Customer] = EARLIER('Table'[Customer])&&'Table'[P] = EARLIER('Table'[P])),'Table'[Channel],"&")
Then you can create a measure for count customers in different combinations :
Measure = DISTINCTCOUNT('Table'[Customer])
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EctwMJyl_SlIlaH8QUDWjDkBozN9GGPqSQH4JwBdWSAtGw?e=WIcDJq
If this post help, please consider accept it as the solution to help other member find it more quickly.
Best Regards,
Dedmon Dai
Hi @v-deddai1-msft ,
Thank you so much for the help. Calculations looks perfect except that the date filter in the calculation needs to be dynamic based on selection by user. We need 2 date parameters (P1 & P2) for user to select different periods. Then based on these period selection at run time, we need to calculate combinations. Could you please help ..
Thank you.
Hi @v-deddai1-msft ,
Thank you so much for your response, but requirement is bit different. I need to divide customers into different sets based on their shopping channel for both period 1 and period 2. For example if customer shopped in both online and offline for the selected period p1, his P1 label need to fixed at customer level to online & offline. I need to group customers into different combinations (Customers shopped on only online, online+offline, only offline etc ..) for both period 1 and period 2 parameters.
Hi @Anonymous ,
You can create a new calculated column to determine parameter for each customer by date:
parameter = SWITCH(TRUE(),'Table'[Date]>=DATE(2020,10,1) &&'Table'[Date]<=DATE(2020,10,2),"P1",'Table'[Date]=DATE(2020,10,3),"P2")
Then you can create a matrix for your expected output:
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EV4vA8GQfvZKhJexAEPZeEEBPxh0xugnaN36TCNPOdUCww?e=ccvw1L
If this post help, please consider accept it as the solution to help other member find it more quickly.
Best Regards,
Dedmon Dai
User | Count |
---|---|
90 | |
88 | |
87 | |
79 | |
49 |
User | Count |
---|---|
151 | |
143 | |
111 | |
74 | |
55 |