User list assigned to orders

[flashbi2]

Hi,

I am struggling with the possibility of displaying the number of orders assigned to a specific person in a bar chart.

Lets assume i have got such data:

order	person
1	John
2	Karen
3	peter
4	Greg
5	John. Karen
6	John. Karen, Greg
7	Karen, Greg

I want to show on a graph how many orders are assigned only to John, Karen, Peter, etc.
When I add these columns to bar chart I got 7 columns but I want to have only 4 because there are only 4 people assigned to orders. I tried to split the person column into different columns (person1, person2, person3) but then I got columns with spaces.
Do you have any idea how to achieve this?

Thank you in advance

[SamInogic]

Hi @flashbi2 ,

If we consider your table data from question then we noticed that you need to split out the data based on person separated by comma (,) and dot (.). Also, there are extra spaces with names so need to trim the data.

This needs below steps to be performed in Power Query editor,

1. Select person column and click on transform tab. Within this tab click on Split Column with option “By Delimiter” as shown in the below screenshot.

2. This will ask for delimiter, so select comma “,” and in Advanced options select “Rows” option as shown in below screenshot.

3. Repeat same 2 steps to split column data further using delimiter dot (.).

4. Now trim the data of person column to remove any extra spaces by clicking on Transform and selecting Trim option.

This will result in 4 bars in bar chart.

Thanks!

Inogic Professional Services Division

Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!

Drop an email at crm@inogic.com

Services:  http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[flashbi2]

Hi,

Thanks for your help but is there any other way to do this without duplicating the rows? I have many more other columns and it will affect other metrics. I tried to do this by creating another table with all the users, then building the relationship with the table. I also wrote some DAX formula but nothing worked