How to create a string column out of 3 other string columns

[Yacine_VC]

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	xxx@email.com	Teams	SharePoint
Training 1	31/05/2024	xxx@email.com	Teams	SharePoint
Training 1	31/05/2024	xxx@email.com	Teams	SharePoint
Training 2	26/04/2024	xxx@email.com	SharePoint
Training 2	26/04/2024	xxx@email.com	SharePoint
Training 2	26/04/2024	xxx@email.com	SharePoint
Training 3	19/04/2024	xxx@email.com	Teams	Planner	To Do
Training 3	19/04/2024	xxx@email.com	Teams	Planner	To Do
Training 3	19/04/2024	xxx@email.com	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.

[Ashish_Mathur]

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.

[Yacine_VC]

This worked very well, thanks a lot!

[Ashish_Mathur]

You are welcome.

[v-tangjie-msft · Accepted solution]

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. 

[Tahreem24]

@Yacine_VC Try this new DAX Table,

Table 2 = DISTINCT(UNION(

    SELECTCOLUMNS('Table',"Tool", 'Table'[Tool 1]),

    SELECTCOLUMNS('Table',"Tool", 'Table'[Tool 2]),

    SELECTCOLUMNS('Table',"Tool", 'Table'[Tool 3]))

)

[Yacine_VC]

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.