Forum Discussion
How to create a string column out of 3 other string columns
Hello everyone,
I'm having an issue with my PBI model : it's supposed to count the number of people attending to formations. The report is based on an excel file, filled by the trainers. It looks like that :
| Training name | Date | Participant e-mail | Tool 1 | Tool 2 | Tool 3 |
| Training 1 | 31/05/2024 | [email protected] | Teams | SharePoint | |
| Training 1 | 31/05/2024 | [email protected] | Teams | SharePoint | |
| Training 1 | 31/05/2024 | [email protected] | Teams | SharePoint | |
| Training 2 | 26/04/2024 | [email protected] | SharePoint | ||
| Training 2 | 26/04/2024 | [email protected] | SharePoint | ||
| Training 2 | 26/04/2024 | [email protected] | SharePoint | ||
| Training 3 | 19/04/2024 | [email protected] | Teams | Planner | To Do |
| Training 3 | 19/04/2024 | [email protected] | Teams | Planner | To Do |
| Training 3 | 19/04/2024 | [email protected] | Teams | Planner | To Do |
I created a measure to count the numer of times each tool appears in the three "tool" columns at the time.
Now to filter the measure above in a graphic, I would like to create a column (maybe in a new table?) listing each string appearing in the three "tool" columns at least once, and without any duplicate.
That would look like that :
Teams |
| SharePoint |
| Planner |
| To Do |
| OneDrive |
| Viva Engage |
I haven't been able to generate such a column, I can only concatenate the three "tool" columns cells, but that's not what I'm looking for.
Would someone know how I could manage to create this column ?
Best regards.
Hi Yacine_VC ,
First of all thanks to Tahreem24 for the quick reply and solution. He did a great job solving your initial problem. I have some suggestions for your follow-up questions. If I have misunderstood you, please create a new case, we suggest a case to solve only one problem, because it can get good help and give a better reference for other users!
The recommendations are as follows:
(1)We can create a table.
Table 2 = FILTER(DISTINCT(UNION( SELECTCOLUMNS('Table',"Tool", 'Table'[Tool 1]), SELECTCOLUMNS('Table',"Tool", 'Table'[Tool 2]), SELECTCOLUMNS('Table',"Tool", 'Table'[Tool 3])) ),[Tool]<>BLANK())(2)We can create a measure.
Measure = COUNTROWS(FILTER(ALLSELECTED('Table'),[Tool 1] in VALUES('Table 2'[Tool]) || [Tool 2]in VALUES('Table 2'[Tool]) || [Tool 3] in VALUES('Table 2'[Tool])))(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
It worked very well, thank you very much.
But it seems that it doesn't filter the tool names as I expected in my graphic :
I was expecting the graphic to display to filter the number of sessions attended throught the names of the tools of each session : 13 values for Planner, 56 values for Power BI, 12 values for Outlook, etc.
The X axis is filled with the tool list I created with your help, while the Y axis is filled with a measure accounting the number of rows where each tool name appears.
The measure code is this one :
Nb spectateurs par outil = COUNTROWS(FILTER(Liste_sessions,NOT(ISBLANK(Liste_sessions[Outil 1])) || NOT(ISBLANK(Liste_sessions[Outil 2]) || NOT(ISBLANK(Liste_sessions[Outil 3])))))So I'm wondering if the problem doesn't come from my data model : I can create a relationship between the column listing all tools and only one of the tool columns from my session list table.Would you know how I can make it work?Best regards.
Hi Yacine_VC ,
First of all thanks to Tahreem24 for the quick reply and solution. He did a great job solving your initial problem. I have some suggestions for your follow-up questions. If I have misunderstood you, please create a new case, we suggest a case to solve only one problem, because it can get good help and give a better reference for other users!
The recommendations are as follows:
(1)We can create a table.
Table 2 = FILTER(DISTINCT(UNION( SELECTCOLUMNS('Table',"Tool", 'Table'[Tool 1]), SELECTCOLUMNS('Table',"Tool", 'Table'[Tool 2]), SELECTCOLUMNS('Table',"Tool", 'Table'[Tool 3])) ),[Tool]<>BLANK())(2)We can create a measure.
Measure = COUNTROWS(FILTER(ALLSELECTED('Table'),[Tool 1] in VALUES('Table 2'[Tool]) || [Tool 2]in VALUES('Table 2'[Tool]) || [Tool 3] in VALUES('Table 2'[Tool])))(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Ashish_Mathur
Super User
Hi,
In the Query Editor, select all columns other than the Tools columns, right click and select "Unpivot Other Columns". You may remove the attribute column and rename the Value column to Tools. Write this measure
Total = countrows(Data)
Hope this helps.
This worked very well, thanks a lot!
- Ashish_Mathur
You are welcome.
