Turning concatenate quick measure into calculated column

[FrancescaHawksw]

I have a excel data file that looked like the below. I need to present this back in a table visual with other data values and where I can filter by skills to identify who has what skills. 

Employee ID	Name	Skills
1234	Sam	Collaboration, strategy, teamwork, leadership
3456	Jane	Agile working, teamwork, engineering
6789	Jack	Change management, engineering, sales

To do this I split the skills column by the comma deliminator and then unpivoted so I had multiple rows per person each with one skills (E.g. below)

Employee ID	Name	Attribute	Value
1234	Sam	Skills.1	Collaboration
1234	Sam	Skills.2	strategy
1234	Sam	Skills.3	teamwork
1234	Sam	Skills.4	leadership
3456	Jane	Skills.1	Agile working
3456	Jane	Skills.2	teamwork

I then used the quick measure feature to concatenate those skills values so I could create a table visual with a row for each person where one of the columns was their list of skills, with the intention that I could filter by the skills using a slicer. 

The issue I have is that when I filter by a skill (for example, teamwork, it would include individuals who did not have teamwork as a skill and show the skill column for them as blank). 

I think this is because it is a measure, so I wonder if I should change it to be a calculated column but I can't work out the dax code of this. 

Any help appreciated! Thank you

[v-sathmakuri]

Hi @FrancescaHawksw ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

[v-sathmakuri]

Hi  @FrancescaHawksw , 

Thank you for reaching out to Microsoft Fabric Community.

You can use the DAX table below to generate the expected output. I have also attached the PBIX file for your reference. Please find the PFBS included as well.

Add a new table using the below DAX :

ExpandedSkills =

SELECTCOLUMNS(

    ADDCOLUMNS(

        GENERATE(

            'SLT development plans (Skills)',

            VAR SkillList = SUBSTITUTE('SLT development plans (Skills)'[Skills], ", ", "|")

            RETURN

                SELECTCOLUMNS(

                    ADDCOLUMNS(

                        GENERATESERIES(1, PATHLENGTH(SkillList)),

                        "Skill", PATHITEM(SkillList, [Value])

                    ),

                    "EmployeeID", 'SLT development plans (Skills)'[Employee ID],

                    "Name1", 'SLT development plans (Skills)'[Name],

                    "Attribute", "Skills." & [Value],

                    "Value", [Skill]

                )

        ),

        "Index", [Value]

    ),

    "EmployeeID", [Employee ID],

    "Name1", [Name],

    "Attribute", [Attribute],

    "Value", [Value]

)

 

 If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you!!

[FrancescaHawksw]

Hi @v-sathmakuri 

Thanks for responding, I may not have been clear but that is not my expected output. 

As an output I need to be able to create a table visual that has the following (see below) AND where I can apply slicers to filter by specific skills, i.e. if I filtered on collaboration Sam would show in the table visual but someone who does not have that skill would not 

Employee ID	Name	Skills	Development areas
1234	Sam	Collaboration, strategy, teamwork, leadership	Agile working, teamwork, engineering

My raw data looks like the above but PowerBI does not allow multiple unsummarised columns in a table visual, which is why I split the text by the comma and unpivoted the columns to create a concatenate measure. However the measure is not filtering as expected (including individuals who do not have the selected skill). 

A previous responder said that creating a calculated column with concatenateX could work but I can't seem to do this where only the skills relevant to individual are combined in a list, not all skills in the unpivoted column. 

Thanks 
Francesca 

[v-sathmakuri · Accepted solution]

Hi @FrancescaHawksw ,

Thank you for your response and clearly mentioning your requirements.

I have created a below calculated table which will get the distinct skills

SkillList = DISTINCT(ExpandedSkills[Value])

Nex, i have established a relationship between expanded table and sklllist table using value field ( which represents skills)

 
Then added a calculated column in expanded skills column, Used this to get the full list of skills per person, This column will repeat the full list of skills per person for each row.

ConcatenatedSkills =

CALCULATE (

    CONCATENATEX (

        VALUES(ExpandedSkills[Value]),

        ExpandedSkills[Value],

        ", ",

        ExpandedSkills[Value],

        ASC

    ),

    ALLEXCEPT(ExpandedSkills, ExpandedSkills[EmployeeID])

)

In the slicer add the field from newly created table SkillList and in the table add the fields from ExpandedSkills table, this will give the expected results because of the active relationship. Attached the pbix file for reference.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you

[FrancescaHawksw]

Hi Akash, 

Thanks for responding, that is what I am struggling with. 

Below is the quick measure I had but when I copy this into the formula bar as a calculated column it does show the specific skills for each person, just a repeat of the same skills (the first 3 listed) so it seems it isn't connecting back to the employee ID. Also will I be able to use mutliple calculated columns in a table visual? As that is my end goal

Quick measure DAX:

List of Skills values =

VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('SLT development plans (Skills)'[Skills])

VAR __MAX_VALUES_TO_SHOW = 5

RETURN

    IF(

        __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,

        CONCATENATE(

            CONCATENATEX(

                TOPN(

                    __MAX_VALUES_TO_SHOW,

                    VALUES('SLT development plans (Skills)'[Skills]),

                    'SLT development plans (Skills)'[Skills],

                    ASC

                ),

                'SLT development plans (Skills)'[Skills],

                ", ",

                'SLT development plans (Skills)'[Skills],

                ASC

            ),

            ", etc."

        ),

        CONCATENATEX(

            VALUES('SLT development plans (Skills)'[Skills]),

            'SLT development plans (Skills)'[Skills],

            ", ",

            'SLT development plans (Skills)'[Skills],

            ASC

        )

    )

[Akash_Varuna]

@FrancescaHawksw Create a calculated column using the CONCATENATEX function, which will concatenate the skills for each employee into one column. This will allow you to filter by skills and prevent blank values from showing.